Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    7

    Unanswered: Calling export from SP- Need help urgent

    Hi Guys,
    I am oracle guy donot know about Db2. so please help me.

    Can I call db2 export from db2 procedure. I want to read all tables which needs to be exported and generate export command for each one and call those export commands from DB2 PROCEDURE.

    Is it possible ? if no can u please suggest me any other alternative/code.

    Thanks
    PTP

  2. #2
    Join Date
    Apr 2003
    Posts
    9
    If you want to do this outside of a stored proc, I use SQL to generate my export statements. I am not an "expert", but for me I usually use DB2MOVE, or if not, I run the query

    SELECT 'EXPORT TO C:\'||CHAR(NAME,25)||'.IXF OF IXF MESSAGES '||CHAR(NAME,25)||'.MSG SELECT * FROM "'||CHAR(CREATOR,25)||'"."'||CHAR(NAME,25)||'" ; ' FROM SYSIBM.SYSTABLES WHERE TYPE = 'T' AND CREATOR NOT IN ('SYSCAT','SYSIBM','SYSSTATS');

    IXF is the international standard format, and is best for exporting data if possible

    Depending on what version of DB2 you are running, you may have to add some creators to that "NOT IN" clause, and you will probably have to manipulate the results to get rid of spaces between file names and file extensions, but it should be relatively painless. Again, I am not an expert, just someone who wanted to share. There may be better ways to do this, but this works for me.

  3. #3
    Join Date
    May 2003
    Posts
    7
    Thanks for reply.
    ok so i can generate export SQL statements.
    now i want to run that automatically ...you know what i mean , may be SP (scheulded at time) or cron job.

    in oracle , unix cron job executes sqlcommands by spooling all commands and calling that spool file in sqlplus as
    sqlplus -s myspoolfile.

    can somebody share code in db2. thanks a ot in advance.

    PTP

    Originally posted by actiii
    If you want to do this outside of a stored proc, I use SQL to generate my export statements. I am not an "expert", but for me I usually use DB2MOVE, or if not, I run the query

    SELECT 'EXPORT TO C:\'||CHAR(NAME,25)||'.IXF OF IXF MESSAGES '||CHAR(NAME,25)||'.MSG SELECT * FROM "'||CHAR(CREATOR,25)||'"."'||CHAR(NAME,25)||'" ; ' FROM SYSIBM.SYSTABLES WHERE TYPE = 'T' AND CREATOR NOT IN ('SYSCAT','SYSIBM','SYSSTATS');

    IXF is the international standard format, and is best for exporting data if possible

    Depending on what version of DB2 you are running, you may have to add some creators to that "NOT IN" clause, and you will probably have to manipulate the results to get rid of spaces between file names and file extensions, but it should be relatively painless. Again, I am not an expert, just someone who wanted to share. There may be better ways to do this, but this works for me.

  4. #4
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    59
    put all ur sql statements in a file and save it as a dot sql file

    create a shell file which contains the following


    # this is the shell file
    connect to database user db2inst1 using db2inst1
    db2 -stvf myqueries.sql > myqueriesoutput.log

    # end- shell script

    and all u have to do now is schedule the cron to run this shell file as and when u need.


    HTH

    brat



    Originally posted by ptp
    Thanks for reply.
    ok so i can generate export SQL statements.
    now i want to run that automatically ...you know what i mean , may be SP (scheulded at time) or cron job.

    in oracle , unix cron job executes sqlcommands by spooling all commands and calling that spool file in sqlplus as
    sqlplus -s myspoolfile.

    can somebody share code in db2. thanks a ot in advance.

    PTP

  5. #5
    Join Date
    May 2003
    Posts
    7
    Thanks for all reply. I will check options below.

    PTP

    Originally posted by brat4
    put all ur sql statements in a file and save it as a dot sql file

    create a shell file which contains the following


    # this is the shell file
    connect to database user db2inst1 using db2inst1
    db2 -stvf myqueries.sql > myqueriesoutput.log

    # end- shell script

    and all u have to do now is schedule the cron to run this shell file as and when u need.


    HTH

    brat

  6. #6
    Join Date
    May 2003
    Posts
    7
    I think it is time to go back to DB2 school. May be i am asking too many questions...
    how to open OS file and write export commands to it in DB2 ?

    any good DB2 UDB book is available on stored procedures ?


    Thanks in advance ...


    PTP

  7. #7
    Join Date
    May 2011
    Posts
    15
    Hi All,

    Please will you tell me how to get rid of spaces between file name and file extension.

    thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •