IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Cannot reload dataset back into table??


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Sat Aug 01, 2009 3:38 pm
Reply with quote

did You look at ....
did You meditate on ...
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
Reply with quote

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

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Sat Aug 01, 2009 5:07 pm
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
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Mon Aug 03, 2009 9:10 pm
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Mon Aug 03, 2009 9:43 pm
Reply with quote

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

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

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

and
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
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

Moderator Emeritus


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

PostPosted: Tue Aug 04, 2009 8:58 pm
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
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

Moderator Emeritus


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

PostPosted: Tue Aug 04, 2009 9:45 pm
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
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
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
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
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
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
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Aug 27, 2009 9:59 pm
Reply with quote

Hello,

One way is to use a bit of your own code. . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts batch SFTP job using AOPBATCH unable ... All Other Mainframe Topics 7
No new posts Reading Empty Dataset DFSORT/ICETOOL 5
No new posts Mainframe Dataset Binary or ASCII Mainframe Interview Questions 4
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
Search our Forums:

Back to Top