SQL QUERY to unload XML rec from DB2 using DSNTIAUL

gopikrishnan1984 · New User Joined: 21 Sep 2008 Posts: 8 Location: India

Hi!

I have a DB2 table with one column is defined as XML. Can you please give me a QUERY to unload records from the table using the utility DSNTIAUL.

Nic Clouston · Posted: Wed Dec 11, 2013 6:47 pm

What problems do you have with what you have tried after referencing the manuals as required?

gopikrishnan1984 · New User Joined: 21 Sep 2008 Posts: 8 Location: India

Hi,

Copied the JCL used,

//PS01 EXEC PGM=IKJEFT01
//SYSTSIN DD *
DSN SYSTEM(DB2E)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
LIB('DBOPEN.LOAD.LIB') -
PARMS('SQL')
END
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSREC00 DD DSN=TSHRPD.JPOC656R.POCEPA00.QTR.BK21,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(10,5),RLSE),
// DCB=(LRECL=0,RECFM=FB,BLKSIZE=0)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT * FROM <TABLE NAME> WHERE <CONDITION>
//*

After SUBMISSION, job abended and i have copied abend information,

DESCRIPTION: S30A-1C - AN ERROR OCCURRED IN R FORM OF FREEMAIN. REFER TO MESSAGE IEA705I DISPLAYED IN JES MSGLOG OF THE JOB THAT ENDED. THE 2 BYTES RETURN CODE TO THE RIGHT OF THE ABEND CODE IDENTIFIES THE ERROR.
POSSIBLE CAUSES: 1C - ZERO LENGTH IS NOT SPECIFIED ON A SUBPOOL FREEMAIN.

I'm not sure , whether the used unload query will work DSNTIAUL for XML datatypes. Please advice and provode me the SQL query to unload.

Akatsukami · Posted: Wed Dec 11, 2013 9:07 pm

Have you considered using the IBM online utility (or third-party equivalent) DSNUTILB rather than DSNTIAUL?

gopikrishnan1984 · New User Joined: 21 Sep 2008 Posts: 8 Location: India

I haven't tried DSNUTILB utility, in our shop we are using DSNTIAUL. I tried many unload with DSNTIAUL and it went fine. Only problem with XML data UNLOAD, I'm facing the issue.

Nic Clouston · Posted: Thu Dec 12, 2013 3:51 pm

Have you tried increasing the region size - an x0A is a memory problem which may, but may not, be resolved by extra memory. The fact that it is an IBM utility means that if the problem persists then you should report it to IBM. You should certainly check their knowledge base to see if a similar problem has already been reported. Your DBAs and sytem support should be referred to.

gylbharat · Posted: Thu Dec 12, 2013 6:33 pm

Can you post the complete job output for the failed run?

gopikrishnan1984 · New User Joined: 21 Sep 2008 Posts: 8 Location: India

I have copied the abend information,

****************************
* * S-30A *
* A B E N D *------------*
* * SYSTEM *
****************************
*****************************************************************************************
* DESCRIPTION: S30A-1C - AN ERROR OCCURRED IN R FORM OF FREEMAIN. *
* REFER TO MESSAGE IEA705I DISPLAYED IN JES MSGLOG OF THE JOB THAT *
* ENDED. THE 2 BYTES RETURN CODE TO THE RIGHT OF THE ABEND CODE *
* IDENTIFIES THE ERROR. *
* *
* POSSIBLE CAUSES: 1C - ZERO LENGTH IS NOT SPECIFIED ON A SUBPOOL FREEMAIN. *
* *
* TO CORRECT: 1. REGISTER 0 AT ENTRY TO ABEND HAS THE LENGTH OF STORAGE TO BE *
* FREED, ITS HIGH ORDER BYTE CONTAINS THE SUBPOOL NUMBER *
* TO BE FREED. *
* 2. REGISTER 1 AT ENTRY TO ABEND HAS THE ADDRESS OF STORAGE TO *
* BE FREED. *
* 3. THE SUBPOOL AREA TO BE FREED MUST BE EXPLICITLY DESCRIBED. *
* 4. THE SYSTEM FREES SUBPOOL 0 WHEN A JOB STEP ENDS. *
* 5. CHECK FOR PROGRAMMING ERRORS THAT INCORRECTLY MODIFIED *
* THE FREEMAIN MACRO EXPANSION. *
*****************************************************************************************
************************************************************************************************************************************
* LAST SQL RETURN CODE: *
* *
*DSNT408I SQLCODE = -501, ERROR: THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN *
*DSNT418I SQLSTATE = 24501 SQLSTATE RETURN CODE *
************************************************************************************************************************************

CAPD112W SYMBOLIC INFORMATION NOT FOUND FOR PROGRAM "UNKWN1 ".

ABENDING INSTRUCTION

0A0A SVC SUPERVISOR CALL

INTERRUPT OCCURRED AT ADDRESS 80060AB2 AT OFFSET +001692 IN PROGRAM UNKWN1
LAST KNOWN I/O OR CALL OCCURRED AT ADDRESS 00060A26 AT OFFSET +001606 IN PROGRAM UNKWN1
THE Z/OS BREAKING EVENT ADDRESS IS 00000000 00FF7FF2 IN UNKNOWN PROGRAM

gylbharat · Posted: Mon Dec 16, 2013 4:01 pm

I think this is not DSNTIAUL.

Its a cobol program - UNKWN1

The SQL CODE is -551

gopikrishnan1984 · New User Joined: 21 Sep 2008 Posts: 8 Location: India

HI,

In the mail chain, i have already copied the JCL used, where i'm executing DSNTIAUL utility. The Job abend with S30A-1C and 1C - ZERO LENGTH IS NOT SPECIFIED ON A SUBPOOL FREEMAIN. it shows some issue with the FREEMAIN allocation.

I have tried by increasing the REGION size, but the result is same. Please suggest to overcome the abend S30A-1C.

Nic Clouston · Posted: Mon Dec 16, 2013 9:09 pm