6.3 Data movement through flat files

 < Day Day Up > 



6.3 Data movement through flat files

The main principle of data movement is to export the Oracle data to flat files in a well defined format, and to load or import the data to DB2 UDB. We can use any tool or write any application to achieve this. This section discusses the data movement through flat files.

The script examples included in this section can be download from IBM Redbook Web site. Please see Appendix G, "Additional material" on page 415 for details.

Before writing data into flat file, ensure that the maximum file size of your operating system is big enough. On AIX you get the actual file size limit in blocks with the command:

    ulimit -a 

To set the file size limit to unlimited on AIX, enter as root:

    ulimit -f -1 

6.3.1 Moving data using MTK

In 4.6, "The Generate Data Transfer Scripts task" on page 108, we explain how to use MTK to generate scripts for data unload and data load; the correlation of scripts and table definitions of the source and target defined within MTK. MTK allows you to move data through its GUI on-line without generated scripts as well. When moving data on-line, MTK does not use the generated scripts when deploying the load/import from the GUI.

6.3.2 Using shell scripts

We use UNIX shell scripts in our example, which uses the Oracle SQL*Plus utility to download the data from Oracle tables to flat files. These scripts can be run only in UNIX platforms, and should be run under a user that has the Oracle environment set. These scripts can only be run for Oracle tables containing CHAR, VARCHAR2, NUMBER data types. For exporting LOB columns, special programs has to be written with Oracle APIs. Example 6-1 shows the main data_unload.sh script. This script reads the table name from table_list_file parameter specified when invoking the script and constructs the dynamic query and estimates the line size using two other awk scripts. Once the query is constructed, the query is fed to the SQL*Plus utility, the output is spooled, and stored in the output file with delimited ASCII (DEL) format table_name.DAT.

Example 6-1: The data_unload.sh script

start example
 #!/bin/ksh # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # Shell script:  data_unload.sh # # Syntax:        data_unload.sh <table_list_file> # # Starting from an flat file containing a list of all the table, # extracts data from Oracle for each table and writes data into # a file named table_name.DAT, formatted in columns # # This script uses the awk command with the following awk command files: # desc.awk   formats the query command files using RTRIM and DECODE #            to obtain a column-formatted output # count.awk  computes the total length of a record # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # Define the environment variables for the oracle user and password ORACLE_USR=user1 ORACLE_PWD=user1 # # Start of main program # Loop on all the tables listed in the input file for i in `cat $1` do   # Define some environment variables for temporary files   export OUTFILE=$i.DAT   DSCFILE=$i.dsc   SQLFILE=$i.sql   VARFILE=$i.var   ALLFILE=$i.all   POSFILE=$i.pos   rm -f $OUTFILE   rm -f $DSCFILE   rm -f $SQLFILE   # Extract the table description from Oracle catalog   sqlplus -s $ORACLE_USR/$ORACLE_PWD <<EOF >/dev/null 2>&1   clear columns   clear breaks   set pagesize 100   set newpage 1   set feedback off   spool $DSCFILE   desc $i   EOF   # Cut head and tail from the file containing the descriptions of the tables   # Change also the NOT NULL clause in a blank string   # and cut the blanks in the first column   tail +3 $DSCFILE | sed 's/NOT NULL/ /; s/^ //' > $DSCFILE.tmp1   NL=`wc -l < $DSCFILE.tmp1`   NLM1=`expr $NL - 1`   head -$NLM1 $DSCFILE.tmp1 > $DSCFILE.tmp2   cp $DSCFILE.tmp2 $VARFILE   # Change the data types, leaving in the file the respective lengths   # It is assumed that 41 bytes are enough to contain the significative   # part of the NUMBER fields   sed -e 's/ VARCHAR2(/ /' \   -e 's/ NUMBER(/ /' \   -e 's/ NUMBER/ 41/' \   -e 's/ INTEGER(/ /' \   -e 's/ INTEGER/ 41/' \   -e 's/ CHAR(/ /' \   -e 's/ CHAR/ 1/' \   -e 's/ RAW(/ /' \   -e 's/ VARCHAR(/ /' \   -e 's/)//' \   -e 's/\([0-9]*\)\,\([0-9 ]\)*/\1/' \   $DSCFILE.tmp2 > $DSCFILE.tmp3   mv $DSCFILE.tmp3 $DSCFILE   rm -f $DSCFILE.tmp*   # Compute the record length of the table   # using the count.awk awk script   LS=`awk -f count.awk $DSCFILE`   # Prepare the heading of the query statement on the table   # by echoing the statements into the sql file   echo "clear columns" > $SQLFILE   echo "clear breaks" >> $SQLFILE   echo "set pagesize 50000" >> $SQLFILE   echo "set linesize $LS" >> $SQLFILE   echo "set feedback off" >> $SQLFILE   echo "set heading off" >> $SQLFILE   echo "set space 0" >> $SQLFILE   echo "set newpage 1" >> $SQLFILE   echo "spool $OUTFILE" >> $SQLFILE   echo "select '' " >> $SQLFILE   # Append to the query statement file the list of the table fields   # to obtain the column layout, using the desc.awk awk script   awk -f desc.awk $VARFILE >> $SQLFILE   # Append to the query statement file the "from" clause   # and the closing instructions   echo "from $i;" >> $SQLFILE   echo "spool off" >> $SQLFILE   echo "quit" >> $SQLFILE   # Execute the query statement   sqlplus -s $ORACLE_USR/$ORACLE_PWD @$SQLFILE >/dev/null 2>&1   # Cut the first line from the output file   tail +2 $OUTFILE > $OUTFILE.tmp   mv $OUTFILE.tmp $OUTFILE   # Change the DATE data type into its DB2 external length, 26 bytes   sed 's/ DATE/ 26/' $DSCFILE > $DSCFILE.tmp1   mv $DSCFILE.tmp1 $DSCFILE done 
end example

The table name is read from table_list_file and described using the DESCRIBE TABLE command, and output is directed to a describe file TABLE_NAME.dsc. From the describe file, the SELECT query is constructed using the awk script file desc.awk, which produces the TABLE_NAME.sql file. Example 6-2 shows the desc.awk script. The TABLE_NAME.sql file contains the SELECT statement on which executed produces the result that can be exported to a delimited ASCII file type, which can be used for the LOAD or IMPORT utilities. The character data types are enclosed by the ~ character and the DATE data types are converted to equivalent DB2 TIMESTAMP data type. The query uses concatenation string || to concatenate the column values to single line.

Example 6-2: The desc.awk script

start example
 BEGIN {} {   if ($2 == "DATE")     print " || rtrim(DECODE("$1",NULL,' ',TO_CHAR("$1",'YYYY-MM-DD-HH24.MI.SS') || '.000000'),26)"   if (substr($2,1,4) == "CHAR")     print " ||'~'||rtrim("$1")||'~,' "   if (substr($2,1,8) == "VARCHAR2")     print " ||'~'||rtrim("$1")||'~,' "   if (substr($2,1,6) == "NUMBER")     print " ||rtrim("$1")||','" } 
end example

The data_unload.sh script also uses another awk script count.awk to count the length of each column to estimate the output line size for SQL*Plus utility. Example 6-3 shows the count.awk script. Once the SELECT statement and the line size is ready, the SQL*Plus environment is set using SET PAGESIZE, SET LINESIZE commands. The command SET PAGESIZE, SET LINESIZE, SET FEEDBACK manipulates the output. The SET LINESIZE is set with the output produced by the count.awk script. Then the SQL*Plus runs the TABLE_NAME.sql file and spools the output to TABLE_NAME.dat output flat file. This file is in delimited ASCII (DEL) format and can be used by the LOAD or IMPORT utility. For example to export the ACCOUNTS table enter the table name into a file say table.lst, edit the data_unload.sh script enter the Oracle user name ORACLE_USR and password ORACLE_PWD and invoke using the command:

 sh data_unload.sh table.lst 

Example 6-3: The count.awk script

start example
 BEGIN { total=0 } {   if ($2 == "DATE")     total +=26   else     total += $2+4 } END { print total } 
end example

This produces the accounts.dsc, accounts.sql, accounts.var, and accounts.DAT file. To load the data using LOAD utility, use the command:

 DB2 LOAD FROM accounts.DAT OF DEL MODIFIED BY CHARDEL~ INSERT INTO ACCOUNTS 

To load the data using IMPORT utility use the command:

 DB2 IMPORT FROM accounts.DAT OF DEL MODIFIED BY CHARDEL~ INSERT INTO ACCOUNTS 

6.3.3 Using Oracle's stored procedures

In this example we explain an Oracle stored procedure export_table, written by this redbook team to demonstrate how to unload the data from Oracle using this stored procedure, and to load the data to DB2. As in our previous example, this stored procedure can only be used for CHAR, VARCHAR2, NUMBER, and DATE data types. As in the shell script, this stored procedure gets the table name as the input parameter, constructs the SELECT query for output, and exports the table data to a output flat file. This output file format is also delimited in a ASCII file format.

The advantage of using this stored procedure is it can be used in Windows and NIX platforms. The output file is placed under the directory specified by the UTL_FILE_DIR initialization parameter. The UTL_FILE_DIR initialization parameter specifies the directory for PL/SQL file I/O. So, it is a must that this initialization parameter be specified in the Oracle instance before using this stored procedure. Example 6-4 shows the export_table stored procedure script.

Example 6-4: Procedure to export_ table data

start example
 /*************************************************************/ /* This stored procedure accept the table name as input      */ /* and exports the data into flat file identified by the     */ /* UTL_FILE_DIR with the format acceptable by the DB2        */ /* IMPORT utility or LOAD utility as Delimited ASCII file    */ /* Note : this procedure can be used for the table with data */ /* types CHAR,VARCHAR2 and NUMBER.                           */ /*************************************************************/ CREATE OR REPLACE PROCEDURE export_table(    i_table_name IN VARCHAR2                       -- table name to be exported ) IS   stmt_1      VARCHAR2(4000) := 'select ';        -- first part of select   stmt_2      VARCHAR(50) := ' as linecol from '; -- second part of the select   stmt_cursor INTEGER;                            -- statement handle   linecol     VARCHAR2(4000);                     -- output buffer for utl_file   ret         INTEGER;                            -- dbms_sql handle   filepath    VARCHAR(40):='c:\oracle';           -- path for output file   filename    VARCHAR(40);                        -- output filename   filemode    CHAR(1):='w';                       -- output file mode for write   filelnsz    INTEGER := 4000;                    -- max file line size   dtype_excp  EXCEPTION;   fhandle     utl_file.file_type;                 -- file handle for utl_file   CURSOR col_crsr(tab_col_name IN VARCHAR2) IS      SELECT column_name, data_type      FROM user_tab_columns      WHERE table_name = tab_col_name; BEGIN    stmt_1 := stmt_1||'/*parallel('||i_table_name||',4)*/'||''''||'''';    /*********************************************************/    /* Build the select statement                            */    /*********************************************************/    FOR my_rec IN col_crsr(i_table_name) LOOP      IF my_rec.data_type = 'DATE' THEN        stmt_1 := stmt_1 || '|| rtrim(DECODE('|| my_rec.column_name                         || ',NULL,'|| ''' '''|| ',TO_CHAR('                         || my_rec.column_name ||','''                         || 'YYYY-MM-DD-HH24.MI.SS'||''')))';      ELSIF my_rec.data_type = 'CHAR' THEN        stmt_1 := stmt_1 || '||'''||'~'||''''||'||rtrim('                         || my_rec.column_name||')||'''||'~,'||'''';      ELSIF my_rec.data_type = 'VARCHAR2' THEN        stmt_1 := stmt_1 || '||'''||'~'||''''||'||rtrim('                         || my_rec.column_name||')||'''||'~,'||'''';      ELSIF my_rec.data_type = 'NUMBER' THEN        stmt_1 := stmt_1 || '||rtrim('||my_rec.column_name                         || ')||'''||','||'''';      ELSE RAISE dtype_excp;      END IF;    END LOOP;    stmt_2 := stmt_2 || i_table_name;    stmt_1 := stmt_1 || stmt_2;    /*********************************************************/    /* Execute the statement and open the cursor             */    /*********************************************************/    stmt_cursor := dbms_sql.open_cursor;    dbms_sql.parse(stmt_cursor,stmt_1,dbms_sql.native);    dbms_sql.define_column(stmt_cursor,1,linecol,4000);    ret := dbms_sql.execute(stmt_cursor);    filename:=i_table_name||'.DAT';    fhandle:= utl_file.fopen(filepath,filename,filemode,filelnsz);    /*********************************************************/    /* Fetch the rows and write it to output file            */    /*********************************************************/    WHILE dbms_sql.fetch_rows(stmt_cursor)>0 LOOP      dbms_sql.column_value(stmt_cursor,1,linecol);      utl_file.put_line(fhandle,linecol);    END LOOP;    /********************************************************/    /* Close the cursor and file                            */    /********************************************************/    dbms_sql.close_cursor(stmt_cursor);    utl_file.fclose(fhandle);    EXCEPTION    WHEN dtype_excp THEN dbms_output.put_line('Invalid Data type'); END; 
end example

This stored procedure use the Oracle DBMS_SQL package to construct the select statement and retrieve the result set. It uses UTL_FILE Oracle package to create the output file, open it, and write the output data to the output file. The UTL_FILE can only write to output file created under UTL_FILE_DIR identified directory. The filepath variable in the stored procedure has to be edited and specified the value given for UTL_FILE_DIR initialization parameter. In our example, it points to C:\Oracle as the UTL_FILE_DIR parameter. The output file created will be named as TABLE_NAME.DAT file. For example, to export the data in the ACCOUNTS table, the stored procedure is called using the command:

 CALL EXPORT_TABLE('ACCOUNTS') 

Note that the table name should be entered in uppercase. When the stored procedure is called successfully, it produces the output file ACCOUNTS.DAT file. To load the data using LOAD utility, use the command:

 DB2 LOAD FROM ACCOUNTS.DAT OF DEL MODIFIED BY CHARDEL~ INSERT INTO ACCOUNTS 

To load the data using IMPORT utility use the command:

 DB2 IMPORT FROM ACCOUNTS.DAT OF DEL MODIFIED BY CHARDEL~ INSERT INTO ACCOUNTS 



 < Day Day Up > 



Oracle to DB2 UDB Conversion Guide2003
Oracle to DB2 UDB Conversion Guide2003
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 132

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net