Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2011
    Posts
    24

    Unanswered: Writing to file DB2 Z/OS

    I'am trying to add some basic logging at end of some stored procedures.
    Yes its best to insert to a table for this purpose, but at the moment I need to write to a file. Did come across writing C/Java UDF on net, but wondering if there are simpler methods in DB2 Z/os Version 10??

    May be something like this?
    EXPORT TO file.txt of DEL ' select * from table_name';

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    What type of stored procs? External or SQL? I'm not aware of any method to write to files from SQL stored procs. For External store procs, you could use standard host language I/O methods provided the DD is allocated in the WLM. If the DD is not allocated, you would need to dynamically allocate the file.

  3. #3
    Join Date
    Nov 2011
    Posts
    24
    These are normal SQL procedures that are run in DB2.
    The simple EXPORT TO command like the one in this recent thread would suffice, but I doubt if it is supported in DB2 z/os - didn't work.
    http://www.dbforums.com/db2/1672257-...rt-import.html

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    ADMIN_DS_WRITE procedure - does this help ?

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Nov 2011
    Posts
    24
    Thanks sathyaram.
    IBM Information Management Software for z/OS Solutions Information Center

    admin_ds_write looks bit complex - not sure what values to provide for all parameters.(Google has very few results, no examples)

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    It looks like you have to insert your message text to CGTT SYSIBM.TEXT_REC_INPUT and then call this procedure.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    I haven't been able to make ADMIN_DS_WRITE work for me. I haven't spent a lot of time with it because I don't like the RECFM=FB and LRECL=80 limitation, and more importantly, I have a working solution to dynamically allocate files. Here's what I tried in the CLP this morning. The ADMIN_DS_WRITE stored proc completed successfully, but did not write the records:

    Code:
    db2 => update command options using c off;
    DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
    db2 => list command options;
    
         Command Line Processor Option Settings
    
     Backend process wait time (seconds)        (DB2BQTIME) = 1
     No. of retries to connect to backend        (DB2BQTRY) = 60
     Request queue wait time (seconds)          (DB2RQTIME) = 5
     Input queue wait time (seconds)            (DB2IQTIME) = 5
     Command options                           (DB2OPTIONS) =
    
     Option  Description                               Current Setting
     ------  ----------------------------------------  ---------------
       -a    Display SQLCA                             OFF
       -c    Auto-Commit                               OFF
       -d    Retrieve and display XML declarations     OFF
       -e    Display SQLCODE/SQLSTATE                  OFF
       -f    Read from input file                      OFF
       -i    Display XML data with indentation         OFF
       -l    Log commands in history file              OFF
       -m    Display the number of rows affected       OFF
       -n    Remove new line character                 OFF
       -o    Display output                            ON
       -p    Display interactive input prompt          ON
       -q    Preserve whitespaces & linefeeds          OFF
       -r    Save output to report file                OFF
       -s    Stop execution on command error           OFF
       -t    Set statement termination character       ON
       -v    Echo current command                      OFF
       -w    Display FETCH/SELECT warning messages     ON
       -x    Suppress printing of column headings      OFF
       -z    Save all output to output file            OFF
    
    db2 => INSERT INTO SYSIBM.TEXT_REC_INPUT (ROWNUM,TEXT_REC) VALUES (1,'TEST RECOR
    D 1');
    DB20000I  The SQL command completed successfully.
    db2 => INSERT INTO SYSIBM.TEXT_REC_INPUT (ROWNUM,TEXT_REC) VALUES (2,'TEST RECOR
    D 2');
    DB20000I  The SQL command completed successfully.
    db2 => INSERT INTO SYSIBM.TEXT_REC_INPUT (ROWNUM,TEXT_REC) VALUES (3,'TEST RECOR
    D 3');
    DB20000I  The SQL command completed successfully.
    db2 => SELECT * FROM SYSIBM.TEXT_REC_INPUT;
    
    ROWNUM      TEXT_REC
    
    ----------- --------------------------------------------------------------------
    ------------
              1 TEST RECORD 1
    
              2 TEST RECORD 2
    
              3 TEST RECORD 3
    
    
      3 record(s) selected.
    
    db2 => CALL SYSPROC.ADMIN_DS_WRITE (1,'TEST.TEMP.DSWRITE',' ','R','N',?,?);
    RETURN_CODE: 0
    MSG:
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
     "ADMIN_DS_WRITE" RETURN_STATUS: 0
    
    db2 =>
    I can see the allocation messages for the dataset in the WLM (no error messages), but when I browse the dataset it's empty. Same problem using processing options 'A' and 'ND'. Hopefully, techday will have a better outcome.

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Simplest way, I believe, is to write a second stored procedure which should be an external one, let's say written in COBOL. Just pass it two arguments: the text to be written to file, and the dataset name to write to.
    Call that stored procedure from your original SQL procedure.
    --_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
    Nov 2011
    Posts
    24
    Thanks for the example Sharon.
    Got closer to doing it, but could'nt do the insert to SYSIBM.TEXT_REC_INPUT
    as I dont have permissions on sys tables.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by jsharon1248 View Post
    db2 => update command options using c off;
    db2 => INSERT INTO SYSIBM.TEXT_REC_INPUT (ROWNUM,TEXT_REC) VALUES (1,'TEST RECORD 1');
    db2 => INSERT INTO SYSIBM.TEXT_REC_INPUT (ROWNUM,TEXT_REC) VALUES (2,'TEST RECORD 2');
    db2 => INSERT INTO SYSIBM.TEXT_REC_INPUT (ROWNUM,TEXT_REC) VALUES (3,'TEST RECORD 3');
    db2 => CALL SYSPROC.ADMIN_DS_WRITE (1,'TEST.TEMP.DSWRITE',' ','R','N',?,?);
    This worked for me: the three lines were inserted into the (pre-existing) dataset. But there is indeed "public" access to that temp.table:
    GRANT ALL ON TABLE SYSIBM.TEXT_REC_INPUT TO PUBLIC
    (Seems like this was issued during the migration to DB2 9.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What I often do is either writing to stdout/stderr and looking at the WLM job output or to write to a file on HFS using regular fopen()/fwrite()/... Of course, it depends on your environment if those options are available to you.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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