Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL QUERY to unload XML rec from DB2 using DSNTIAUL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
gopikrishnan1984

New User


Joined: 21 Sep 2008
Posts: 8
Location: India

PostPosted: Wed Dec 11, 2013 1:13 pm    Post subject: SQL QUERY to unload XML rec from DB2 using DSNTIAUL
Reply with quote

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.
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Wed Dec 11, 2013 6:47 pm    Post subject:
Reply with quote

What problems do you have with what you have tried after referencing the manuals as required?
Back to top
View user's profile Send private message
gopikrishnan1984

New User


Joined: 21 Sep 2008
Posts: 8
Location: India

PostPosted: Wed Dec 11, 2013 8:01 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Dec 11, 2013 9:07 pm    Post subject:
Reply with quote

Have you considered using the IBM online utility (or third-party equivalent) DSNUTILB rather than DSNTIAUL?
Back to top
View user's profile Send private message
gopikrishnan1984

New User


Joined: 21 Sep 2008
Posts: 8
Location: India

PostPosted: Thu Dec 12, 2013 2:13 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Thu Dec 12, 2013 3:51 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Dec 12, 2013 6:33 pm    Post subject:
Reply with quote

Can you post the complete job output for the failed run?
Back to top
View user's profile Send private message
gopikrishnan1984

New User


Joined: 21 Sep 2008
Posts: 8
Location: India

PostPosted: Mon Dec 16, 2013 12:54 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Dec 16, 2013 4:01 pm    Post subject:
Reply with quote

I think this is not DSNTIAUL.

Its a cobol program - UNKWN1

The SQL CODE is -551

Quote:
-501 THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN
Explanation: The application program attempted either to fetch using a cursor or to close a cursor at a time when
the specified cursor was not open.
System action: The statement cannot be processed.
Programmer response: Check for a previous SQL return code that might have closed the cursor.
v Commit and rollback operations close cursors.
v SQL return codes -404, -652, -679, -802, -901, -904, -909, -910, -911, -913, and -952 might force the cursor to close.
After the cursor is closed, any fetches or close cursor statements will receive SQLCODE -501.
If no previous SQL return codes have been issued, correct the logic of the application program to ensure that the
cursor is open at the time the FETCH or CLOSE statement is processed.
SQLSTATE: 24501



Code:

*DSNT408I SQLCODE = -501, ERROR: THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN *
*DSNT418I SQLSTATE = 24501 SQLSTATE RETURN CODE *
Back to top
View user's profile Send private message
gopikrishnan1984

New User


Joined: 21 Sep 2008
Posts: 8
Location: India

PostPosted: Mon Dec 16, 2013 8:35 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Mon Dec 16, 2013 9:09 pm    Post subject:
Reply with quote

Quote:
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

Considering that it is, supposedly, an IBM utility program that is failing with a program error you will need to take it to IBM as only they can fix it. However, before doing so see if your system people can identify why the program name is UNKNOWN as highlighted above. Someone may have "done a naughty" and mucked up the loadlib.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us