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
 

 

SQLCODE=-452 - Unloading DB2 Table using JCL for CLOB type

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

New User


Joined: 20 May 2013
Posts: 7
Location: India

PostPosted: Tue May 21, 2013 7:05 pm    Post subject: SQLCODE=-452 - Unloading DB2 Table using JCL for CLOB type
Reply with quote

Hi All,

I am trying to unload DB2 table using below JCL SYSIN and I am get SQLCODE -452 due to invalid symbols in table values like '$' and '_'. If anyone already faced this problem then assist me. Thanks.

Note: 6000 records are unloaded out of 8000 rows. 2000 records are skipped due to -452 sqlcode .

Code:
 //SYSIN DD *                                                     
   TEMPLATE UNLD   DSN BZXS.BZXSKG1.ITS0134D.SYSREC               
           UNIT SYSDA  DISP (NEW, CATLG, CATLG) SPACE (30,10) CYL 
   TEMPLATE LOBT  DSN BZXS.BZXSKG1.ITS0134D.CLOBREC               
           UNIT SYSDA  DISP (NEW, CATLG, CATLG) SPACE (30,10) CYL 
   TEMPLATE LDMAP    DSN BZXS.BZXSKG1.ITS0134D.SYSPUNCH           
           UNIT SYSDA  DISP (NEW, CATLG, CATLG) SPACE (1,1) TRK   
   UNLOAD TABLESPACE D135DEV1.ITS0134D                             
     PUNCHDDN LDMAP                                               
     UNLDDN UNLD   EBCDIC                                         
     MAXERR 5000                                                   
     FROM TABLE O135DEV1.IPP_MSG                                   
     (MSG_ID  CHAR(32),                                           
      PART_KEY_NUM SMALLINT,                                       
      PAYLOAD_DATA VARCHAR CLOBF LOBT                             
      )                                                           
 /*   

ERROR:
DSNU283I  -DZ1T 137 16:13:17.50 DSNUULVA - LOB ERROR                   
      SQLCODE = -452                                                   
      SQLERRM = BZXS.BZXSKG1.ITS0134D.CLOBREC(EAPTPT0F)13             
      SQLSTATE= 428A1                                                 
      SQLERRP = DSNOLFRV                                               
      SQLERRD = 00000124 00000000 00000000 FFFFFFFF 00000000 00000000 
DSNU283I  -DZ1T 137 16:13:17.51 DSNUULVA - LOB ERROR                   
      SQLCODE = -452                                                   
      SQLERRM = BZXS.BZXSKG1.ITS0134D.CLOBREC(EAPTPT0S)13             
      SQLSTATE= 428A1                                                 
      SQLERRP = DSNOLFRV                                               
      SQLERRD = 00000124 00000000 00000000 FFFFFFFF 00000000 00000000     
Code'd
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Tue May 21, 2013 9:30 pm    Post subject: Reply to: SQLCODE=-452 - Unloading DB2 Table using JCL for C
Reply with quote

why the small font for the JCL ???
Back to top
View user's profile Send private message
sreekanth gundrathi

New User


Joined: 20 May 2013
Posts: 7
Location: India

PostPosted: Tue May 21, 2013 10:16 pm    Post subject: Re: Reply to: SQLCODE=-452 - Unloading DB2 Table using JCL f
Reply with quote

enrico-sorichetti wrote:
why the small font for the JCL ???


I copied the JCL and pasted in the post. Now, It is not possible to change the font because I posted this in the morning. Are you unable to view the JCL?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 21, 2013 10:19 pm    Post subject:
Reply with quote

Hello and welcome to the forum,

Please re-read your post. The screen info has been Code'd. Please use the Cosde tag for data that needs a fixed-font (such as JCL, Code, Data, etc.

Which version/release of DB2 is being used?
Back to top
View user's profile Send private message
sreekanth gundrathi

New User


Joined: 20 May 2013
Posts: 7
Location: India

PostPosted: Tue May 21, 2013 10:37 pm    Post subject:
Reply with quote

DB2 Version 10 Release 1 for Z/OS.

Thanks,
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 21, 2013 11:36 pm    Post subject:
Reply with quote

Hello,

Read here:
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.codes%2Fsrc%2Ftpc%2Fn452.htm

I do not see anything about a symbol error in that manual.

I did see this apar:
https://www-304.ibm.com/support/docview.wss?uid=swg1PM43689

which mentions something similar.

You may need to contact db2 support.
Back to top
View user's profile Send private message
sreekanth gundrathi

New User


Joined: 20 May 2013
Posts: 7
Location: India

PostPosted: Tue May 21, 2013 11:45 pm    Post subject:
Reply with quote

Hello,

I have already read those two links which is not helping me to resolve the issue. Can you please let me know how to contact db2 support?

Thanks
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


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

PostPosted: Tue May 21, 2013 11:50 pm    Post subject:
Reply with quote

The DB2 support in your shop, Sreekanth-kun; your DBAs, or the sysprogs responsible for installing and maintaning DB2.

BTW, you did notice that in SQLERRM you have an invalid MVS DSN that doesn't contain a dollar sign, yes?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed May 22, 2013 12:04 am    Post subject:
Reply with quote

Hello,

Many organizations only have a few people who are permitted to contact IBM directly. As Akatsukami suggests, contact your DBA or other local DB2 support. If none exists, ask your manager the proper method for your group to contact IBM Support.
Back to top
View user's profile Send private message
sreekanth gundrathi

New User


Joined: 20 May 2013
Posts: 7
Location: India

PostPosted: Wed May 22, 2013 12:11 am    Post subject:
Reply with quote

Hello,

The DSN is correct because it has got 6000 records unloaded from DB2 table after completion of Job.

We have created a Ticket for DBA and they are working on it.

Thanks a lot for the support.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed May 22, 2013 7:17 pm    Post subject:
Reply with quote

Hi,

In this error - the error code is 13.

Code:

DSNU283I  -DZ1T 137 16:13:17.50 DSNUULVA - LOB ERROR                   
      SQLCODE = -452                                                   
      SQLERRM = BZXS.BZXSKG1.ITS0134D.CLOBREC(EAPTPT0F)13             
      SQLSTATE= 428A1                                                 
      SQLERRP = DSNOLFRV                                               
      SQLERRD = 00000124 00000000 00000000 FFFFFFFF 00000000 00000000 


As per the z/OS error guide

Quote:

-452 UNABLE TO ACCESS THE FILE REFERENCED BY HOST VARIABLE variable-position. REASON
CODE: reason-code
Explanation: An error was encountered while DB2 was accessing, or attempting to access, the file referenced by the
specified host variable.
variable-position
Indicates the position of the host variable that referenced the file, as the nth host variable, where n is the
value.
reason-code
A numeric value that indicates the reason for the error:

13 If errors were encountered when DB2 attempted to OPEN, READ, WRITE, or CLOSE an HFS file or
BSAM data set, there might be a system completion code on the console to indicate the reason that
DB2 was unable to perform the requested operation.



Please let us know what is the system completion code for the failed job?
Might be job is abending due to no space in the PDS (most common problem).
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu May 23, 2013 3:06 pm    Post subject:
Reply with quote

Hi Sreekanth,

Delete the PDS and create PDSE with a bigger primary quantity, it should be available in Single DASD volume. Usually, you get this error when its not able to extend.

Code:
TEMPLATE LOBT  DSN BZXS.BZXSKG1.ITS0134D.CLOBREC               
           UNIT SYSDA  DISP (NEW, CATLG, CATLG) SPACE (500,100) CYL


Thanks,
Sushanth
Back to top
View user's profile Send private message
sreekanth gundrathi

New User


Joined: 20 May 2013
Posts: 7
Location: India

PostPosted: Thu May 23, 2013 11:32 pm    Post subject:
Reply with quote

Hi Sushanth,

I deleted the PDS and ran the job with more space(P-900,S-900) but still it is throwing same SQLCODE -452.

It is having issues with Data in Development region tables. So, we are trying find the invalid symbols in the table values which may solve the issue.

Thank you.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 24, 2013 12:28 am    Post subject:
Reply with quote

Hello,

You might write a bit of code to read the actual table(s) and look for any invalid values . . .

Suggest you read all of the columns into host variables that are redefined as one large character field and then check that field for any invalid value(s).
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri May 24, 2013 11:19 am    Post subject:
Reply with quote

Sreekanth,

After you increased the primary, did it fail with same clob member as before or different one.

I know, the error message does'nt indicate, it failed due to insufficient space. Its so confusing, spent nearly a week looking at it, but this happens to be the problem. Just increase the primary to 1500 to 2000, just to make the SMS to select a volume that has more free-space on it, the secondary can be 200.

You need to work with DBA's & Storage Team to get resolved.

Thanks,
Sushanth
Back to top
View user's profile Send private message
sreekanth gundrathi

New User


Joined: 20 May 2013
Posts: 7
Location: India

PostPosted: Wed Jun 26, 2013 7:51 pm    Post subject:
Reply with quote

Hello All,

I added DSNTYPE(LIBRARY) to the below file and the problem is resolved.

TEMPLATE LOBT DSN BZXS.BZXSKG1.ITS0134D.CLOBREC
UNIT SYSDA DISP (NEW, CATLG, CATLG) SPACE (30,10) CYL
DSNTYPE(LIBRARY)

Thank you All.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jun 27, 2013 10:40 am    Post subject:
Reply with quote

Thanks sreekanth for letting us know.
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
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Query using SUBSTRING on a column of ... sasanka DB2 5 Mon Jul 04, 2016 1:01 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm


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