Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: Need Help Exporting from QMF

    Hi everyone,

    I am new to DB2, and I need help exporting query results to a txt file from QMF. I have been using a command, "Export report to filename" and then doing a KSUB command to export the file to our FTP site.

    However, this method doesn't work for me because the export file will come out with garbage characters, and it will wrap data around to a 2nd line if I have too many export columns. This makes it impossible to bring the file into Excel for reporting.

    I found out that there is another method to export called SPUFI. I can't find any documentation on this. Can anyone help me?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    SPUFI (SQL processor using file input) is a way of submitting SQL via a TSO panel and having the results sent to a file. It is under the DB2I panel in DB2 under TSO. SPUFI uses a PDS member as an input file and you can specify the output file. However the output file is in the form of a report, and you will have to strip off the column headers, page break info, etc. Contact your DBA for assistance (If you are the DB2 z/OS you are in deep trouble).

    A better way to do this is to use the DB2 for Windows Adminstrative client connected to your mainframe DB2 via a DCS connection (you will need a DB2 Connect license to do this). You connect to the remote DB2 z/OS database and then export the data on your PC to DEL format. You will need to work with your DBA to get you set up to do this.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    A better way to do this is to use the DB2 for Windows Adminstrative client connected to your mainframe DB2 via a DCS connection (you will need a DB2 Connect license to do this). You connect to the remote DB2 z/OS database and then export the data on your PC to DEL format. You will need to work with your DBA to get you set up to do this.
    Two other possibilities, entirely on z/OS:
    - similar to export on LUW, you could use the UNLOAD utility on z/OS.
    UNLOAD can directly export in comma-delimited ("del") format.
    Then (as before) FTP that file to your MS-Windows environment.
    The only limitation of UNLOAD is that is doesn't speak SQL: so you can just unload a single physical table, no joins or subqueries (be it that you can limit the number of rows or columns).
    - Use the DSNTEP4 program instead of SPUFI or QMF.
    There are fewer limitations to the output file format (line length & number of lines) than with SPUFI, and it also writes to file (just like SPUFI or UNLOAD).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2009
    Posts
    46
    Quote Originally Posted by Marcus_A
    SPUFI (SQL processor using file input) is a way of submitting SQL via a TSO panel and having the results sent to a file. It is under the DB2I panel in DB2 under TSO. SPUFI uses a PDS member as an input file and you can specify the output file. However the output file is in the form of a report, and you will have to strip off the column headers, page break info, etc. Contact your DBA for assistance (If you are the DB2 z/OS you are in deep trouble).

    A better way to do this is to use the DB2 for Windows Adminstrative client connected to your mainframe DB2 via a DCS connection (you will need a DB2 Connect license to do this). You connect to the remote DB2 z/OS database and then export the data on your PC to DEL format. You will need to work with your DBA to get you set up to do this.

    Oh boy, your response really makes me feel clueless here. Just some background here. Our database is housed at a remote data warehouse so I really hate to bug the DBA there. They just hired me for writing queries with my background in SQL. Unfortunately, I have no knowledge of DB2. I've been looking at IBM's website, and just awful jumbled mess of documentation.

    I use an application called IBM Personal Communications and then log into TSO. Can you give me more info as to which screen or option SPUFI is in? I enter Q on the main menu to get to QMF. I tried SP for SPUFI and DB, but nothing happens. Maybe I'm not on the right track here. Any info would be appreciated. Thanks!

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rockdave35
    I use an application called IBM Personal Communications and then log into TSO.
    OK.
    Can you give me more info as to which screen or option SPUFI is in?
    On our system one has to give the command "DB2" to enter the DB2I main panel, from which you go to the SPUFI main panel with the command "1". There is no guarantee that it's DB2 on your system: try DB2I or D as well.
    For using DSNTEP4, you would need some inside help, I'm afraid.
    Actually, QMF could be your best option (if you fail on SPUFI): it certainly must have the wanted "export" possibilities. I'm not familiar enough with QMF for pointing you in the good direction.
    QMF info can be found starting from IBM - DB2 Query Management Facility - DB2 Query Management Facility - Software ; the manuals are under IBM - DB2 Query Management Facility - Library ; reference guide: http://publib.boulder.ibm.com/epubs/pdf/c1896850.pdf
    The SQL Reference Guide for DB2 v8 is http://publib.boulder.ibm.com/epubs/pdf/dsnsqj16.pdf

    If what you need to extract is from a single table, and you need a comma-separated output, using UNLOAD is certainly an option for you. If you've found the DB2I start panel, type command "8" (instead of 1 for SPUFI) and you arrive on the utilities panel where you just enter the UNLOAD command and a few other things; if that panel is configured correctly, this will probably be the simplest path for you. Have a look at the UNLOAD chapter in the DB2 Utitility Guide: http://publib.boulder.ibm.com/epubs/pdf/dsnugj16.pdf
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Peter.Vanroose
    OK.
    On our system one has to give the command "DB2" to enter the DB2I main panel, from which you go to the SPUFI main panel with the command "1". There is no guarantee that it's DB2 on your system: try DB2I or D as well.
    For using DSNTEP4, you would need some inside help, I'm afraid.
    Actually, QMF could be your best option (if you fail on SPUFI): it certainly must have the wanted "export" possibilities. I'm not familiar enough with QMF for pointing you in the good direction.
    QMF info can be found starting from IBM - DB2 Query Management Facility - DB2 Query Management Facility - Software ; the manuals are under IBM - DB2 Query Management Facility - Library ; reference guide: http://publib.boulder.ibm.com/epubs/pdf/c1896850.pdf
    The SQL Reference Guide for DB2 v8 is http://publib.boulder.ibm.com/epubs/pdf/dsnsqj16.pdf

    If what you need to extract is from a single table, and you need a comma-separated output, using UNLOAD is certainly an option for you. If you've found the DB2I start panel, type command "8" (instead of 1 for SPUFI) and you arrive on the utilities panel where you just enter the UNLOAD command and a few other things; if that panel is configured correctly, this will probably be the simplest path for you. Have a look at the UNLOAD chapter in the DB2 Utitility Guide: http://publib.boulder.ibm.com/epubs/pdf/dsnugj16.pdf
    Considering he is not a DBA and doesn't know about SPUFI, the chances of him running UNLOAD are slim. He may not even know what JCL is.

    You can export data in QMF, but only in an IXF format that is not compatable with DB2 LUW IXF format.

    I have had to do this many times and the best way if you have any real quantity of data, is to catalog the remote DB2 z/OS database using DB2 for Windows client and do an export to DEL format.

    To get the job done, he must contact the DBA or other experienced DB2 developer, unless he wants to copy and paste data from the QMF session onto his PC.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by rockdave35
    I use an application called IBM Personal Communications and then log into TSO. Can you give me more info as to which screen or option SPUFI is in? I enter Q on the main menu to get to QMF. I tried SP for SPUFI and DB, but nothing happens. Maybe I'm not on the right track here. Any info would be appreciated. Thanks!
    I originally posted "It is under the DB2I panel in DB2 under TSO."

    So once you get into TSO, try DB2 then look for DB2I. It may be different because some shops change the panels around.

    However, I would not advise that you attempt to use SPUFI on your own without help from someone, or you could end up doing something that will get you fired. You must understand TSO, PDS and sequential datasets on z/OS among other things to do this. If you don't know TSO and z/OS, get some help.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    Considering he is not a DBA and doesn't know about SPUFI, the chances of him running UNLOAD are slim. He may not even know what JCL is.
    If the DB2I panels are setup correctly, one doesn't actually need to know JCL to run UNLOAD.
    When the panels are "changed around" or the necessary customisation has not been done, you are out of luck.
    Here are the steps (in summary; I can give details if necessary):
    1. Go to the SPUFI panel and remember the file names in entries 1 and 4.
    2. Hit ENTER, write the UNLOAD statement (see Utilities manual) and hit F3 twice.
    3. Enter 8, which brings you to the Utilities panel; enter the first remembered file name in entry 4.
    4. Write SUBMIT in entry 1, UNLOAD in entries 2 and 3, and NO in the three remaining entries. Then hit ENTER.
    5. Put the second remembered filename in entry 1 and press ENTER.
    Most likely, you'll see a red message appearing on your screen when the job finishes, hopefully with a MAXCC=0.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Peter.Vanroose
    If the DB2I panels are setup correctly, one doesn't actually need to know JCL to run UNLOAD.
    Oh sure. Someone who knows nothing about JCL or z/OS is going to retrieve the output file and know how to download it? Give me a break.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Feb 2005
    Posts
    1
    I know this is an old thread, buy am posting anyway so that it may help someone who stumbles on this thread.

    You can export data from QMF to a report using the following command:

    EXPORT DATA TO 'dsname' (D=I,O=C

    This will export the data to the specified dsn, in a human readable format.

Posting Permissions

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