+ Reply to Thread
Results 1 to 7 of 7

How to Export mutilpule tables to different files using sql query.

  1. How to Export mutilpule tables to different files using sql query.

    Hi All,

    I want to export multiple tables(table 1,table 2...table n) data to different files(file 1, file 2... file n).

    The base query i used for simple export is "export to abc.xls of del select * from test". And i am succeed to export the data to excel file.

    Now i want to export the all tables in the schema to different files(file names stored in temp table), can any one help me how to write query for that, i tried the build query but failed.


    ------------------------------------------------------------------------------
    SELECT substr(tabname,1,length(rtrim(tabname))) from syscat.tables where tabschema in ABC)

    To retrieve the tables in the particular schema.
    -----------------------------------------------------------------------------

    export to (select filename from temp ) of del select * from ( table name retrieved from the syscat table)


    can anybody explain me how to write the above query.

    thanks



  2. Re: How to Export mutilpule tables to different files using sqlquery.



    Hi,

    As i know we cant export multiple tables in a single export statement. So create one script like this....

    File Name - script.txt

    SET DB_NAME=TESTDB
    SET USER_NAME=TESTUSER
    SET PASSWORD=TESTUSERPWD
    SET BASE_DIR=D:\EXPORT_FILES
    SET LOG_DIR=%BASE_DIR%\LOG
    SET OUT_DIR=%BASE_DIR%\OUT


    ECHO ************************** >> migrate.log
    echo BEGIN >> migrate.log
    date /t >> migrate.log
    time /t >> migrate.log
    DB2 CONNECT TO %DB_NAME% user %USER_NAME% using %PASSWORD%
    DB2 EXPORT TO %OUT_DIR%\prod_COMPANY.sql OF IXF MESSAGES %LOG_DIR%\COMPANY.log SELECT * FROM COMPANY
    DB2 EXPORT TO %OUT_DIR%\prod_RATING_INFO.sql OF IXF MESSAGES %LOG_DIR%\RATING_INFO.log SELECT * FROM RATING_INFO
    DB2 EXPORT TO %OUT_DIR%\prod_TICKER_ATTACHMENT.sql OF IXF MESSAGES %LOG_DIR%\TICKER_ATTACHMENT.log SELECT * FROM TICKER_ATTACHMENT
    DB2 EXPORT TO %OUT_DIR%\prod_TICKER_RESEARCH.sql OF IXF MESSAGES %LOG_DIR%\TICKER_RESEARCH.log SELECT * FROM TICKER_RESEARCH
    DB2 EXPORT TO %OUT_DIR%\prod_XI_RATES_HIST.sql OF IXF MESSAGES %LOG_DIR%\XI_RATES_HIST.log SELECT * FROM XI_RATES_HIST
    DB2 EXPORT TO %OUT_DIR%\prod_COMPANY_RATINGS.sql OF IXF MESSAGES %LOG_DIR%\COMPANY_RATINGS.log SELECT * FROM COMPANY_RATINGS
    DB2 EXPORT TO %OUT_DIR%\prod_COMP_RATE_HISTORY.sql OF IXF MESSAGES %LOG_DIR%\COMP_RATE_HISTORY.log SELECT * FROM COMP_RATE_HISTORY

    DB2 EXPORT TO %OUT_DIR%\prod_INVESTOR.sql OF IXF MESSAGES %LOG_DIR%\INVESTOR.log SELECT * FROM INVESTOR
    DB2 EXPORT TO %OUT_DIR%\prod_INVESTOR_EMAILS.sql OF IXF MESSAGES %LOG_DIR%\INVESTOR_EMAILS.log SELECT * FROM INVESTOR_EMAILS
    DB2 EXPORT TO %OUT_DIR%\prod_INVESTOR_MOM.sql OF IXF MESSAGES %LOG_DIR%\INVESTOR_MOM.log SELECT * FROM INVESTOR_MOM
    DB2 EXPORT TO %OUT_DIR%\prod_INVESTOR_UPLOADS.sql OF IXF MESSAGES %LOG_DIR%\INVESTOR_UPLOADS.log SELECT * FROM INVESTOR_UPLOADS
    DB2 EXPORT TO %OUT_DIR%\prod_INV_CONTACTS.sql OF IXF MESSAGES %LOG_DIR%\INV_CONTACTS.log SELECT * FROM INV_CONTACTS
    DB2 EXPORT TO %OUT_DIR%\prod_INV_SUMMARY.sql OF IXF MESSAGES %LOG_DIR%\INV_SUMMARY.log SELECT * FROM INV_SUMMARY

    DB2 EXPORT TO %OUT_DIR%\prod_USER_TICKERS.sql OF IXF MESSAGES %LOG_DIR%\USER_TICKERS.log SELECT * FROM USER_TICKERS
    DB2 EXPORT TO %OUT_DIR%\QUESTIONNAIRE.sql OF IXF MESSAGES %LOG_DIR%\QUESTIONNAIRE.log SELECT * FROM QUESTIONNAIRE
    DB2 EXPORT TO %OUT_DIR%\prod_TABLE_HEADLINES.sql OF IXF MESSAGES %LOG_DIR%\TABLE_HEADLINES.log SELECT * FROM TABLE_HEADLINES
    DB2 EXPORT TO %OUT_DIR%\prod_TABLE_HEAD_LINES.sql OF IXF MESSAGES %LOG_DIR%\TABLE_HEAD_LINES.log SELECT * FROM TABLE_HEAD_LINES
    DB2 EXPORT TO %OUT_DIR%\prod_TICKER_DATE.sql OF IXF MESSAGES %LOG_DIR%\TICKER_DATE.log SELECT * FROM TICKER_DATE
    DB2 EXPORT TO %OUT_DIR%\prod_CONTACT.sql OF IXF MESSAGES %LOG_DIR%\CONTACT.log SELECT * FROM CONTACT
    DB2 EXPORT TO %OUT_DIR%\prod_USER_ACCOUNT.sql OF IXF MESSAGES %LOG_DIR%\USER_ACCOUNT.log SELECT * FROM USER_ACCOUNT
    DB2 EXPORT TO %OUT_DIR%\prod_USER_ROLES.sql OF IXF MESSAGES %LOG_DIR%\USER_ROLES.log SELECT * FROM USER_ROLES



    date /t >> migrate.log
    time /t >> migrate.log
    ECHO END >> migrate.log
    ECHO ************************** >> migrate.log

    Here i have used IXF File format u could change it according to your requirement.

    Then run this script in command window using this command
    DB2 -VF SCRIPT.TXT

    Thanks
    Ajith G

  3. Re: How to Export mutilpule tables to different files using sql query.

    ajithkumar_g@spanservices.com wrote:

    >
    >
    > Hi,
    >
    > As i know we cant export multiple tables in a single export statement. So
    > create one script like this....
    >
    > File Name - script.txt
    >
    > SET DB_NAME=TESTDB
    > SET USER_NAME=TESTUSER
    > SET PASSWORD=TESTUSERPWD
    > SET BASE_DIR=D:\EXPORT_FILES
    > SET LOG_DIR=%BASE_DIR%\LOG
    > SET OUT_DIR=%BASE_DIR%\OUT
    >
    >
    > ECHO ************************** >> migrate.log
    > echo BEGIN >> migrate.log
    > date /t >> migrate.log
    > time /t >> migrate.log
    > DB2 CONNECT TO %DB_NAME% user %USER_NAME% using %PASSWORD%
    > DB2 EXPORT TO %OUT_DIR%\prod_COMPANY.sql OF IXF MESSAGES
    > %LOG_DIR%\COMPANY.log SELECT * FROM COMPANY DB2 EXPORT TO
    > %OUT_DIR%\prod_RATING_INFO.sql OF IXF MESSAGES %LOG_DIR%\RATING_INFO.log
    > SELECT * FROM RATING_INFO DB2 EXPORT TO
    > %OUT_DIR%\prod_TICKER_ATTACHMENT.sql OF IXF MESSAGES
    > %LOG_DIR%\TICKER_ATTACHMENT.log SELECT * FROM TICKER_ATTACHMENT DB2 EXPORT
    > TO %OUT_DIR%\prod_TICKER_RESEARCH.sql OF IXF MESSAGES
    > %LOG_DIR%\TICKER_RESEARCH.log SELECT * FROM TICKER_RESEARCH DB2 EXPORT TO
    > %OUT_DIR%\prod_XI_RATES_HIST.sql OF IXF MESSAGES
    > %LOG_DIR%\XI_RATES_HIST.log SELECT * FROM XI_RATES_HIST DB2 EXPORT TO
    > %OUT_DIR%\prod_COMPANY_RATINGS.sql OF IXF MESSAGES
    > %LOG_DIR%\COMPANY_RATINGS.log SELECT * FROM COMPANY_RATINGS DB2 EXPORT TO
    > %OUT_DIR%\prod_COMP_RATE_HISTORY.sql OF IXF MESSAGES
    > %LOG_DIR%\COMP_RATE_HISTORY.log SELECT * FROM COMP_RATE_HISTORY
    >
    > DB2 EXPORT TO %OUT_DIR%\prod_INVESTOR.sql OF IXF MESSAGES
    > %LOG_DIR%\INVESTOR.log SELECT * FROM INVESTOR DB2 EXPORT TO
    > %OUT_DIR%\prod_INVESTOR_EMAILS.sql OF IXF MESSAGES
    > %LOG_DIR%\INVESTOR_EMAILS.log SELECT * FROM INVESTOR_EMAILS DB2 EXPORT TO
    > %OUT_DIR%\prod_INVESTOR_MOM.sql OF IXF MESSAGES %LOG_DIR%\INVESTOR_MOM.log
    > SELECT * FROM INVESTOR_MOM DB2 EXPORT TO
    > %OUT_DIR%\prod_INVESTOR_UPLOADS.sql OF IXF MESSAGES
    > %LOG_DIR%\INVESTOR_UPLOADS.log SELECT * FROM INVESTOR_UPLOADS DB2 EXPORT
    > TO %OUT_DIR%\prod_INV_CONTACTS.sql OF IXF MESSAGES
    > %LOG_DIR%\INV_CONTACTS.log SELECT * FROM INV_CONTACTS DB2 EXPORT TO
    > %OUT_DIR%\prod_INV_SUMMARY.sql OF IXF MESSAGES %LOG_DIR%\INV_SUMMARY.log
    > SELECT * FROM INV_SUMMARY
    >
    > DB2 EXPORT TO %OUT_DIR%\prod_USER_TICKERS.sql OF IXF MESSAGES
    > %LOG_DIR%\USER_TICKERS.log SELECT * FROM USER_TICKERS DB2 EXPORT TO
    > %OUT_DIR%\QUESTIONNAIRE.sql OF IXF MESSAGES %LOG_DIR%\QUESTIONNAIRE.log
    > SELECT * FROM QUESTIONNAIRE DB2 EXPORT TO
    > %OUT_DIR%\prod_TABLE_HEADLINES.sql OF IXF MESSAGES
    > %LOG_DIR%\TABLE_HEADLINES.log SELECT * FROM TABLE_HEADLINES DB2 EXPORT TO
    > %OUT_DIR%\prod_TABLE_HEAD_LINES.sql OF IXF MESSAGES
    > %LOG_DIR%\TABLE_HEAD_LINES.log SELECT * FROM TABLE_HEAD_LINES DB2 EXPORT
    > TO %OUT_DIR%\prod_TICKER_DATE.sql OF IXF MESSAGES
    > %LOG_DIR%\TICKER_DATE.log SELECT * FROM TICKER_DATE DB2 EXPORT TO
    > %OUT_DIR%\prod_CONTACT.sql OF IXF MESSAGES %LOG_DIR%\CONTACT.log SELECT *
    > FROM CONTACT DB2 EXPORT TO %OUT_DIR%\prod_USER_ACCOUNT.sql OF IXF MESSAGES
    > %LOG_DIR%\USER_ACCOUNT.log SELECT * FROM USER_ACCOUNT DB2 EXPORT TO
    > %OUT_DIR%\prod_USER_ROLES.sql OF IXF MESSAGES %LOG_DIR%\USER_ROLES.log
    > SELECT * FROM USER_ROLES
    >
    >
    >
    > date /t >> migrate.log
    > time /t >> migrate.log
    > ECHO END >> migrate.log
    > ECHO ************************** >> migrate.log

    You can actually generate such a script with a single SQL statement:

    SELECT 'EXPORT TO /home/db2inst1/export/' || tabname OF IFX ' ||
    'SELECT * FROM "' || tabschema '"."' || tabname '"'
    FROM syscat.tables
    WHERE ...

    Redirect the output to a file and then run this file through the DB2 CLP.


    p.s: EXPORT is not a SQL statement. It is a DB2 command. The main
    difference is that SQL statements can be executed via JDBC/CLI whereas
    commands can not.

    --
    Knut Stolze
    DB2 z/OS Utilities Development
    IBM Germany

  4. Re: How to Export mutilpule tables to different files using sqlquery.

    Hi ajith, Knut thanks for your quick replies,

    i constucted the the query based on your exmaple but while executing the query i am getting complile time errors, i guess errors are related to contcatenation.

    This is the query i have built

    SELECT 'EXPORT TO /home/yyy/xxx/' || tabname OF IFX ' ||
    'SELECT * FROM "' || tabschema '"."' || tabname '"'
    FROM syscat.tables WHERE tabname='TEST' and tabschema='schema1'

    Note: i have changed the directory path and shema and table name. I subsituted correct deatils while execute.

    TABLE NAME: TEST and SCHEMA NAME: Schema1.

    can you please help me.

  5. Re: How to Export mutilpule tables to different files using sqlquery.

    Hi,

    You can make use of the db2move command to export all the tables in a schema to different files.......



  6. Re: How to Export mutilpule tables to different files using sql query.

    bhanu_kiran81@yahoo.co.in wrote:
    > Hi ajith, Knut thanks for your quick replies,
    >
    > i constucted the the query based on your exmaple but while executing the query i am getting complile time errors, i guess errors are related to contcatenation.
    >
    > This is the query i have built
    >
    > SELECT 'EXPORT TO /home/yyy/xxx/' || tabname OF IFX ' ||
    > 'SELECT * FROM "' || tabschema '"."' || tabname '"'
    > FROM syscat.tables WHERE tabname='TEST' and tabschema='schema1'
    >
    > Note: i have changed the directory path and shema and table name. I subsituted correct deatils while execute.
    >
    > TABLE NAME: TEST and SCHEMA NAME: Schema1.
    >
    > can you please help me.
    >

    I have formatted the query for better understanding and corrected minor
    syntax errors:

    SELECT
    'EXPORT TO /home/yyy/xxx/'
    ||
    tabname
    ||
    '.IFX OF IFX '
    ||
    'SELECT * FROM "'
    ||
    tabschema
    ||
    '"."'
    ||
    tabname
    || '"'
    FROM
    syscat.tables
    WHERE
    tabname='ORG'
    and
    tabschema='DB2ADMIN'
    ;


    This query generates following output - I have put this query into file
    test.dml and executed like this:

    db2 -x -t -v- -f test.dml


    Output created was:

    EXPORT TO /home/yyy/xxx/ORG.IFX OF IFX SELECT * FROM "DB2ADMIN"."ORG"


    Jan M. nelken

  7. Re: How to Export mutilpule tables to different files using sql query.

    bhanu_kiran81@yahoo.co.in wrote:

    > i constucted the the query based on your exmaple but while executing the
    > query i am getting complile time errors, i guess errors are related to
    > contcatenation.

    Which compile time error?

    > This is the query i have built
    >
    > SELECT 'EXPORT TO /home/yyy/xxx/' || tabname OF IFX ' ||
    > 'SELECT * FROM "' || tabschema '"."' || tabname '"'

    There is a CONCAT or || operator missing between tabname and '"'.

    > FROM syscat.tables WHERE tabname='TEST' and tabschema='schema1'

    --
    Knut Stolze
    DB2 z/OS Utilities Development
    IBM Germany
+ Reply to Thread
« Previous Thread | Next Thread »