1. Migrate an IBM Db2 to an Oracle Autonomous Database
  2. Migrate the Data

Migrate the Data

You'll use Oracle SQL Developer to migrate the database and to copy the table to Oracle.

Run the Migration Scripts

Run the master.sql script in your Oracle Autonomous Database.

  1. Connect to the Oracle Autonomous Database.
  2. Run the master.sql in SQL*Plus or in Oracle SQL Developer.
    Running the master.sql creates the DB2INST1 user and Emulate user.

Emulate handles the transformation of the Stored Procedures and Functions.

DB2INST1 creates all of the Tables and Views in the autonomous database.

Upload the Data to Storage

Upload the data files from your local system to Oracle Cloud Infrastructure Object Storage in your region.

If the data files are 2 GB or smaller, then you can use the Console to upload the files. If the data files are over 2 GB, you can upload to object storage manually or automatically using the Oracle Cloud Infrastructure (OCI) command-line interface (CLI).

When you use the CLI, you specify the part size of your choice, and Oracle Cloud Infrastructure Object Storage splits the object into parts and performs the upload of all parts automatically. You can choose to set the maximum number of parts that can be uploaded in parallel. By default, the CLI limits the number of parts that can be uploaded in parallel to three. When using the CLI, you do not have to perform a commit when the upload is complete.

  1. To upload files using the Console:
    1. Open the OCI Console, go to Object Storage Details, then click the bucket name to view its details.
    2. Click Upload Objects.
    3. Select the object or objects to upload.

      As you select files to upload, they are displayed in a scrolling list. If you decide that you do not want to upload a file that you have selected, click the X icon to the right of the file name.

      If selected files to upload and files already stored in the bucket have the same name, then messages warning you of an overwrite are displayed.

    4. (Optional) In the Object name prefix field, specify a file name prefix for the file that you have selected to upload.
    5. Click Upload Objects.
      The selected objects are uploaded and displayed in the list of objects in the bucket.
  2. To upload data files from your local system to an object storage bucket using the CLI:
    1. Open a command prompt.
    2. Enter a command similar to the following:
      oci os object put -ns object_storage_namespace -bn bucket_name --file file_path --name object_name --part-size upload_part_size_in_MiB --parallel-
upload-count maximum_number_parallel_uploads
      Replace the following variables with parameters for your environment:
      • object_storage_namespace: The top-level namespace used for the request. If not provided, you can get this parameter by using a call to oci os ns get.
      • bucket_name: The name of the storage bucket.
      • file_path: The complete file path including the file name.
      • object_name: The name of the object. The default value is the file name excluding the path.
      • upload_part_size_in_MiB: The size of each multipart in mibibytes (MiB).
      • maximum_number_parallel_uploads: The maximum number of parts that can be uploaded in parallel. The default is 3.

Create Credentials

Create credentials to access the Autonomous Data Warehouse, then store your object storage credentials in the data warehouse.

SQL Developer can use a wallet that contains client credentials to access the Oracle Autonomous Data Warehouse. To create a wallet, you must have an administrator account for Oracle Autonomous Data Warehouse.

  1. Navigate to the Service Console for Oracle Autonomous Data Warehouse.
  2. Click Administration.
  3. Click Download Client Credentials on the Administration page.
  4. Enter a wallet password and confirm the password in Client Credentials.
  5. Click Download to save the client security credentials zip file.
    You must protect this file to prevent unauthorized database access.
  6. Sign in to the Oracle Cloud Infrastructure Console, click the User icon, and select User Settings.
  7. Click Auth Tokens in the Resources section, then click Generate Token.
  8. Provide a description for the Auth Token and click Generate Token.
    Copy the token when it is displayed.
  9. Click the Copy link and store the token information in a secure location.
    When you close the dialog, you will not be able see the token in the Console again.
  10. Click Close.

Migrate the Data to the Autonomous Database

Use Oracle SQL Developer Web to insert the data into the Oracle Autonomous Data Warehouse.

  1. Navigate to the Service Console for Oracle Autonomous Data Warehouse.
  2. Click Development and then click SQL Developer Web.
  3. Enter the admin user name and password for the Oracle Autonomous Data Warehouse instance, then click Sign In.
  4. Log in as the Admin user to connect from Oracle SQL Developer Web to Oracle Autonomous Data Warehouse.
  5. In Oracle SQL Developer Web, execute the following SQL statements to create object store credentials substituting the indicated parameter and flag.
    SET DEFINE OFF 
BEGIN 
DBMS_CLOUD.CREATE_CREDENTIAL( 
credential_name => 'ADW_CREDENTIALS', 
username => 'adwc_user@example.com', 
password => 'auth token' 
); 
END;
 /
    Replace the following variables with parameters for your environment:
    • ADW_CREDENTIALS: The name of the Autonomous Data Warehouse credential to be stored.
    • adwc_user@example.com: The user name for the autonomous data warehouse user. The user name appears in User Settings. If you're using a federated account, make sure to provide the complete user name for example: service_name/user_name>@example.com.
    • auth token: The authentication token that you created earlier.
  6. Validate the user name and password by listing the files in the object store bucket.
    select object_name, bytes from dbms_cloud.list_objects('Credential Name','https://objectstorage.region.oraclecloud.com/n/tenancy/b/Bucket name/');
BE
    Replace the following variables with parameters for your environment:
    • Credential Name
    • region, tenancy, and Bucket name in your object storage link.
  7. Use Oracle SQL Developer Web or SQL Developer to load data to the table you just created.
    BEGIN   
      DBMS_CLOUD.COPY_DATA(     
table_name =>'ARTIST', 
schema_name => 'DB2INST1',
credential_name => 'ADW_CREDENTIALS ',     
file_uri_list =>object storage link,      
format => json_object('delimiter' value '"#EOFD#"','escape' value true, 'recorddelimiter' value '"<EORD>\n"') );  
END;
/
COMMIT;
    Replace the following variables with parameters for your environment:
    • Table name
    • Schema name
    • Credential Name
    • object storage link
    • EORD
  8. Verify that the data is loaded into the tables. 
    select * from DB2INST1.artist