Oracle Warehouse Builder (OWB) Weblog

This post illustrates natively unloading from other database systems such as Microsoft SQL Server using the Oracle 11g database scheduler's remote agent. This is all driven from the database via PLSQL api calls. The package relies on installing the lightweight agent on the database system, so for example install it on the SQLServer instance (see here for details of the agent). The package below has APIs for

• executing the unloader on the SQLServer node   
• zipping the unloaded data file   
• transferring the file to the Oracle instance for use by the next step

Here is an overview of the architecture for an unload from Microsoft SQLServer to Oracle, the remote agent is the Oracle scheduler remote agent;

The package specification to support steps 1/2/3 above is shown below, this leverages the DBMS_SCHEDULER package to execute jobs (it also relies on the user having execute privileges on DBMS_LOCK);

create or replace package OWB_AGENT_PKG as
  procedure SS_UNLOAD (AGENT_BCP_EXE VARCHAR2, -- the BCP exe location
                       AGENT_LOC VARCHAR2, -- the agent location, host:port
                       AGENT_CRED VARCHAR2, -- the agent scheduler's credential name
                       AGENT_DIR VARCHAR2, -- the directory to unload to
                       SRC_SERVER VARCHAR2, -- the SQL Server instance
                       SRC_USER VARCHAR2, -- the SQL Server user
                       SRC_PWD VARCHAR2, -- the SQL Server password
                       TGT_NAME VARCHAR2, -- file name, .dat and .zip will be appended
                       REC_DELIM VARCHAR2 := '0x0A',
                       FLD_DELIM VARCHAR2 := '\t', -- ideally hex fld delimiter 0x07
                       SQLQ VARCHAR2 ); -- the SQL query to execute
  procedure ZIP_DATA (AGENT_ZIP VARCHAR2, -- location of the zip exe
                      AGENT_LOC VARCHAR2, -- the agent location, host:port
                      AGENT_CRED VARCHAR2, -- the agent scheduler's credential name
                      AGENT_DIR VARCHAR2, -- the directory where unloaded file is
                      TGT_NAME VARCHAR2); -- the name of the tgt
  procedure GET_FILE (AGENT_LOC VARCHAR2,-- the agent location, host:port
                      AGENT_CRED VARCHAR2, -- the agent scheduler's credential name
                      AGENT_DIR VARCHAR2,-- the directory where unloaded file is
                      LOCAL_SQL_DIR VARCHAR2, -- the SQL directory name on tgt
                      TGT_NAME VARCHAR2); -- the tgt name
end OWB_AGENT_PKG;
/

How is this used to execute the unloader?

When the SS_UNLOAD call is made a job will be created on the remote agent to call the BCP unloader, this will execute the query passed as a parameter and unload the result set to a data file. The file is delimited, we will see later how to construct an external table based on this file.

exec OWB_AGENT_PKG.SS_UNLOAD ('c:\progra~1\MI6841~1\90\Tools\binn\bcp.exe', 'agenthostname:agentport', 'TESTCRED', 'c:\temp', 'sqlserver_host', 'username', 'password', 'STG_SS', 'SELECT SALES_DATA.PROD_ID ,SALES_DATA.CUST_ID ,SALES_DATA.CHANNEL_ID ,SALES_DATA.PROMO_ID ,SALES_DATA.QUANTITY_SOLD ,SALES_DATA.AMOUNT_SOLD  FROM demo.dbo.SALES_DATA SALES_DATA ');

How is this used to zip the data file?

The ZIP_DATA routine will compress the unloaded data file from the previous step using the zip.exe passed as a parameter, the -j flag is used for zip so that directory names are not also stored.

 exec OWB_AGENT_PKG.ZIP_DATA ('c:\owb\bin\zip.exe', 'agenthostname:agentport', 'TESTCRED', 'c:\temp', 'STG_SS');

How is this used to transfer the data file?

To transfer the file from the SQLServer instance (for example) to the Oracle target use the GET_FILE routine, the transfer is encrypted and authenticated so the database can be sure of the agent identity.

 exec OWB_AGENT_PKG.GET_FILE ('agenthostname:agentport', 'TESTCRED', 'c:\temp', 'DAT_DIR', 'STG_SS');

The file must be unzipped on the Oracle target, again you can use a scheduler job with a local agent or use the OWB process flow to execute an external activity for the unzip.

How is the stage table defined?

To define the stage table as an external table based on the flat file, use the sample wizard on an extraction. Firstly import the metadata for the file:

Define the record delimiter used for the unload:

Define the field delimiter used for the unload:

Create an external table as the staging table:

Select the file definition to base the external table on:

After deploying the external table, the data can be viewed, this is the data unloaded via the BCP unload:

Download the Unloading Package?

The package providing these data unload capabilities can be found here. The package requires execute privilege on DBMS_LOCK. You will also need CREATE EXTERNAL JOB privilege.

Summary

As you see using the DBMS_SCHEDULER package we can run remote jobs such as unload/zip and transfer the file, these procedures can be leveraged in a mapping or flow to prepare the data files for loading into Oracle. The example illustrates SQL Server, for DB2UDB a similar path can be followed, I have also done this, but posted just the SQL Server version.