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
 

 

Cannot reload dataset back into table??
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Jul 31, 2009 9:20 pm    Post subject: Cannot reload dataset back into table??
Reply with quote

I found something strange.
IF I use the following step to unload data from a table,
Code:
//*------------------------------------------------------------------*
//RECOVER EXEC DSNUPROC,SYSTEM=SUN,UID='EV6954RV',UTPROC=''           
//DSNUPROC.STEPLIB DD DSN=SUN.SDSNLOAD,DISP=SHR                       
//DSNUPROC.SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(30,30),RLSE)               
//DSNUPROC.SYSPUNCH DD DSN=EV6954.SUNUNLD.SYSPUNCH,                   
//            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),           
//            RECFM=FB,LRECL=120,BLKSIZE=1200                         
//DSNUPROC.SYSREC00 DD DSN=EV6954.DS.TM11.UNLOAD,DISP=(,CATLG),       
//          UNIT=SYSDA,                                               
//          SPACE=(CYL,(100,100),RLSE)                                 
//DSNUPROC.SYSIN DD *                                                 
 UNLOAD DATA FROM TABLE LJ84U1.LJ84TM11                               
 PUNCHDDN SYSPUNCH UNLDDN SYSREC00 UNICODE DELIMITED                   
/*                                                                     

THE punch data is as follows.
Code:
    EV6954.SUNUNLD.SYSPUNCH                     
ド  ===>                                         
***************************** データの始め ******
LOAD DATA INDDN SYSREC00 LOG NO  RESUME YES     
 UNICODE CCSID(01208,01208,01208)               
 FORMAT DELIMITED                               
 SORTKEYS               36630                   
 INTO TABLE "LJ84U1"."LJ84TM11"                 
 ( "RCUXA"                                       
        POSITION(*)             CHAR MIXED(018) 
 , "CCUGP"                                       
        POSITION(*)             CHAR MIXED(003) 
 , "RESXA"                                       
        POSITION(*)             CHAR MIXED(018) 
 , "NCUXB"                                       
        POSITION(*)             CHAR MIXED(066) 
 , "UNCUX01"                                     
        POSITION(*)             CHAR MIXED(186) 
 , "UNCUX02"                                     
        POSITION(*)             CHAR MIXED(186)
... ...


But if I load the data and the punch DS back into the original table, the following error message occurred, and the job abend.

Code:
  DSNURPIB - NUMBER OF TASKS CONSTRAINED BY SWA=BELOW                         
@SUN DSNURCON - INPUT FIELD 'RCUXA' INVALID FOR 'LJ84U1.LJ84TM11', ERROR CODE '19'
@SUN DSNURWBF - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=1
  DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E40323'
******************************* BOTTOM OF DATA ********************************


According to DB2 manuals, http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.msgs/dsnu334i.htm
ERROR CODE '19' indicates "Input is too long for the table column after a required CCSID conversion."

The data I used to load into the table was just the data generated when I unload from the table. I really do not understand why the above error would occur!


Another question, if I unload data from table using the following JOB step,

Code:
//*------------------------------------------------------------------*
//RECOVER EXEC DSNUPROC,SYSTEM=SUN,UID='EV6954RV',UTPROC=''           
//DSNUPROC.STEPLIB DD DSN=SUN.SDSNLOAD,DISP=SHR                       
//DSNUPROC.SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(30,30),RLSE)             
//DSNUPROC.SYSPUNCH DD DSN=EV6954.SUNUNLD.SYSPUNCH,                   
//            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),           
//            RECFM=FB,LRECL=120,BLKSIZE=1200                         
//DSNUPROC.SYSREC00 DD DSN=EV6954.DS.TM11.UNLOAD,DISP=(,CATLG),       
//          UNIT=SYSDA,                                               
//          SPACE=(CYL,(100,100),RLSE)                               
//DSNUPROC.SYSIN DD *                                                 
 UNLOAD DATA FROM TABLE LJ84U1.LJ84TM11                               
 PUNCHDDN SYSPUNCH UNLDDN SYSREC00                                   
/*                                                                   
//                                                                   


The PUNCH data was generated like follow.
Code:
  表示    EV6954.SUNUNLD.SYSPUNCH.@                   
  コマンド  ===>                                     
********************************* データの始め *******
LOAD DATA INDDN SYSREC00 LOG NO  RESUME NO REPLACE   
 EBCDIC  CCSID(01027,05035,04396)                     
 SORTKEYS               36630                         
 INTO TABLE "LJ84U1"."LJ84TM11"                       
 WHEN(00001:00002) = X'0025'                         
 ( "RCUXA"                                           
        POSITION(  00003:00008) CHAR MIXED(006)       
 , "CCUGP"                                           
        POSITION(  00009:00009) CHAR MIXED(001)       
 , "RESXA"                                           
        POSITION(  00010:00015) CHAR MIXED(006)       
 , "NCUXB"                                           
        POSITION(  00016:00037) CHAR MIXED(022)       
 , "UNCUX01"               
......                         


I do not understand why ' WHEN(00001:00002) = X'0025' 'appeared in the D/S. Can you explain what this stands for? How is its usage?

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

dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sat Aug 01, 2009 1:18 pm    Post subject:
Reply with quote

well, as we all know, basically there are two ways to unload data from a table/tablespace.

1. using the utility I mentioned above;

2. using the sample program named DSNTIAUL provided by IBM.

If I want to unload data with a specific CCSID code, I'm afraid that the 2nd method cannot do this.

This is the reason why I choose the 1st method to unload data.

Appreciate your help very much!
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Sat Aug 01, 2009 3:38 pm    Post subject: Reply to: Cannot reload dataset back into table??
Reply with quote

did You look at ....
did You meditate on ...
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnsqk15/2.14.8?ACTION=MATCHES&REQUEST=unicode+create&TYPE=FUZZY&SHELF=DSNSHKA3.bks&DT=20081120084907&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT
table 32

if You had it should not have looked strange that
in the unicode case the output column is 18 bytes long
in the ebcdic case the output column is 6 bytes long ...

nothing strange, but simply the consequence of the change of representation from EBCDIC to UNICODE

probably( I said probably) ( not tested )
dropping the EBCDIC table and redefining it as an UNICODE one
the reload would succeed
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sat Aug 01, 2009 4:49 pm    Post subject:
Reply with quote

Thank you very much for your kind reply first.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Sat Aug 01, 2009 5:07 pm    Post subject: Reply to: Cannot reload dataset back into table??
Reply with quote

follow on,

it would be interesting to define a simple table with a few fields in both ways,
and run a dclgen to see what the two declares look like

it should take away all the doubts about the length error icon_biggrin.gif
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sun Aug 02, 2009 1:04 pm    Post subject:
Reply with quote

continue to ask.

As I mentioned in the first floor, I still have no idea about the second question.

Quote:
I do not understand why ' WHEN(00001:00002) = X'0025' 'appeared in the D/S. Can you explain what this stands for? How is its usage?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Mon Aug 03, 2009 7:50 pm    Post subject:
Reply with quote

Can anybody please help to answer my question?

what does the following statement in the SYSPUNCH file stand for?
Code:
WHEN(00001:00002) = X'0025'
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Mon Aug 03, 2009 7:54 pm    Post subject:
Reply with quote

I tried to find the answer from the specification of UNLOAD utility, but eventually I got nothing that can help.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon Aug 03, 2009 9:10 pm    Post subject: Reply to: Cannot reload dataset back into table??
Reply with quote

Code:
 WHEN(00001:00002) = X'0025'                         
 ( "RCUXA"                                           
        POSITION(  00003:00008) CHAR MIXED(006)       
 , "CCUGP"                                           
        POSITION(  00009:00009) CHAR MIXED(001)       
 , "RESXA"                                           
        POSITION(  00010:00015) CHAR MIXED(006)       
 , "NCUXB"                                           
        POSITION(  00016:00037) CHAR MIXED(022)       
 , "UNCUX01"               
......               

in the quoted fragment what is after the ...... ??
incomplete info will not hlp anybody
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Mon Aug 03, 2009 9:29 pm    Post subject:
Reply with quote

HELLO, enrico,
Thanks for your reply first.

the code" ...... " stands for the rest field name of the table. The name format is just the same as before-listed like below
Code:
, "CCUGP"                                           
        POSITION(  00009:00009) CHAR MIXED(001)       
 , "RESXA"                                           
        POSITION(  00010:00015) CHAR MIXED(006)       
 , "NCUXB"                                           
        POSITION(  00016:00037) CHAR MIXED(022)   


After the name & position list of the fields, there is nothing left.
So, I thought it was not necessary to put all the content of the dataset here.
I should have stated more specifically on this. Sorry for that and looking forward to your reply!
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon Aug 03, 2009 9:43 pm    Post subject: Reply to: Cannot reload dataset back into table??
Reply with quote

You can find the info on the WHEN reload parameter, guess where,

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNUGK13/CCONTENTS?SHELF=DSNSHKA3&DN=SC18-9855-03&DT=20081124094808

the LOAD related stuff
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNUGK13/2.14?SHELF=DSNSHKA3&DT=20081124094808

and
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNUGK13/2.14.41?SHELF=DSNSHKA3&DT=20081124094808&CASE=
example 3
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Tue Aug 04, 2009 8:03 pm    Post subject:
Reply with quote

Maybe I did not state myself clearly.

I know the usage of WHEN clasue.

What I want to know is , why X'0025' will be set in the first two columns??
From the unload D/S, we can see , the first two columns are all set to X'0025' .
Does this have some special meaning?


Code:
----+----1----+----2----+----3
----+----F----+----F----+----F
----+----1----+----2----+----3
 -----------------------------
.%A01820               A28289
024333332222222222222224333332
051018200000000000000001282890
 -----------------------------
.%A01971               A28289
024333332222222222222224333332
051019710000000000000001282890
 -----------------------------
.%A03726               A01294
024333332222222222222224333332
051037260000000000000001012940
 -----------------------------
.%A03758               A01335
024333332222222222222224333332
051037580000000000000001013350
 -----------------------------
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 Aug 04, 2009 8:58 pm    Post subject:
Reply with quote

Hello,

It is the length of the data (x'25' = decimal 37)?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Tue Aug 04, 2009 9:08 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

It is the length of the data (x'25' = decimal 37)?


You mean record length?

I think not. Because the total record length is 486bytes.
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 Aug 04, 2009 9:45 pm    Post subject:
Reply with quote

Hello,

Quote:
, "NCUXB"
POSITION( 00016:00037) CHAR MIXED(022)
That kinda jumped out. . . icon_confused.gif
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Wed Aug 05, 2009 1:42 am    Post subject: Reply to: Cannot reload dataset back into table??
Reply with quote

That WHEN thing is useful if you are unloading multiple tables in a single tablespace to a single unload file. So while loading it loads appropriate tables rows into right table.

The number in WHEN is the hex of the OBID of the table.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Wed Aug 05, 2009 9:47 am    Post subject: Re: Reply to: Cannot reload dataset back into table??
Reply with quote

wanderer wrote:
That WHEN thing is useful if you are unloading multiple tables in a single tablespace to a single unload file. So while loading it loads appropriate tables rows into right table.

The number in WHEN is the hex of the OBID of the table.

Yes! Exactly!

I have confirmed it from SYSIBM.SYSTABLES, and it is actually the OBID of the table!

Thank you very much!

But would you please tell me how do you know this? I cannot find a clue from the redbooks and manuals. I'll appreciate it very much if you can tell me the method!
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Thu Aug 06, 2009 3:55 am    Post subject: Re: Reply to: Cannot reload dataset back into table??
Reply with quote

dejunzhu wrote:

But would you please tell me how do you know this? I cannot find a clue from the redbooks and manuals. I'll appreciate it very much if you can tell me the method!


I am not sure what all the manuals you have checked. Is Utility Guide one of them? In Unload section, it clearly describes HEADER OBID option.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Aug 06, 2009 9:42 am    Post subject:
Reply with quote

Yes , I find it.

I'm sorry for my carelessness.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Aug 27, 2009 1:20 pm    Post subject: Re: Reply to: Cannot reload dataset back into table??
Reply with quote

wanderer wrote:
That WHEN thing is useful if you are unloading multiple tables in a single tablespace to a single unload file. So while loading it loads appropriate tables rows into right table.

The number in WHEN is the hex of the OBID of the table.

Now I want to leave the position of HEADER blank and let the data unloaded from table starts from column 1, is this feasible?

I looked for the UNLOAD utility and I cannot find any parameter can do this.

Even if I specify "HEADER NONE" in the UNLOAD control statement, the field still exist and the data starts from column 6.
How can I do to cut away HEADER field?
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts FTP - JCL failed while passing FTP co... Suneetha1612 JCL & VSAM 12 Wed Nov 16, 2016 7:33 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm
No new posts Allocate dataset withour BLKSIZE Susanta CLIST & REXX 2 Thu Sep 22, 2016 11:51 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


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