Writing to file DB2 Z/OS

techday · #1 (**permalink**) 11-28-11, 09:53

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';

jsharon1248 · #2 (**permalink**) 12-01-11, 08:48

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.

techday · #3 (**permalink**) 12-02-11, 06:32

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.
Need Help with Export / Import

sathyaram_s · #4 (**permalink**) 12-02-11, 06:38

ADMIN_DS_WRITE procedure - does this help ?

Sathyaram

techday · #5 (**permalink**) 12-02-11, 07:06

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)

sathyaram_s · #6 (**permalink**) 12-02-11, 07:19

It looks like you have to insert your message text to CGTT SYSIBM.TEXT_REC_INPUT and then call this procedure.

jsharon1248 · #7 (**permalink**) 12-02-11, 09:48

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.

Peter.Vanroose · #8 (**permalink**) 12-02-11, 12:37

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.

techday · #9 (**permalink**) 12-05-11, 01:42

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.

Peter.Vanroose · #10 (**permalink**) 12-05-11, 05:13

Quote:

Originally Posted by jsharon1248

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.)

stolze · #11 (**permalink**) 12-05-11, 17:41

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech