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

Record rejected due to invalid column spec during load
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
shailesh.pawar

New User


Joined: 18 May 2012
Posts: 11
Location: India
PostPosted: Fri Jun 08, 2012 1:05 pm
 Reply with quote
Hi,
I have developed a job that uses DSNUTILB to unload data from DB2 table to seq. dataset and load the data from the seq. dataset to the other DB2 table . While loading the data Job gives MAXCC = 4 and shows error in jhs as :-
Code:
RECORD (1) WILL BE DISCARDED DUE TO INVALID 'X2X' COLUMN SPECIFICATION
RECORD (2) WILL BE DISCARDED DUE TO INVALID 'X2X' COLUMN SPECIFICATION
RECORD (3) WILL BE DISCARDED DUE TO INVALID 'X2X' COLUMN SPECIFICATION

The unloaded data in dataset is as follows :-

Code:
 -----+----1----+----2----+----3----+----4----+----5----
 ***************************** Top of Data ************
  ÙSPD           SPD                                     
  Ù02             MOF                                     
  Ù03             RATE       


following is the load statement used :-

Code:
 LOAD DATA INDDN SYSREC REPLACE 
 LOG YES INTO TABLE             
 XXX.YY_ZZ(         
  X1X POSITION(1:10) CHAR(10)
 ,X2X POSITION(11:*) VARCHAR 
 )
in database table definition :- 1) field X1X is of type char(10)
2) field X2X is of type varchar(40)

What can be the reason behind the errors ??

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

Global Moderator


Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
PostPosted: Fri Jun 08, 2012 3:07 pm
 Reply with quote
If you suspect that your JCL is at fault (those are cards starting // in your job) then how do you expect anyone to help. If, on the other hand you suspect your load control cards to be at fault then why post in the JCL section? They are not JCL. Now, it is a problem in DB2 - either your data, your table definition, your unload control cards or your reload control cards. did you read and understand the manual or did you just copy some other job and hope for the best?
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK
PostPosted: Fri Jun 08, 2012 3:13 pm
 Reply with quote
I guess only one reason. see THIS.

Could you share the DDLs for both of your tables?

Thanks.
Back to top
View user's profile Send private message
shailesh.pawar

New User


Joined: 18 May 2012
Posts: 11
Location: India
PostPosted: Fri Jun 08, 2012 3:15 pm
 Reply with quote
I apologize for it !! I have written the job using JCL hence I thought , I should post it Under " JCL " section . Thanks for your suggestion !! Will post it under "DB2" section.

thank you.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
PostPosted: Fri Jun 08, 2012 3:19 pm
 Reply with quote
Every job uses JCL including started tasks. Do NOT repost - someone will, possibly, move it.

TOO LATE, I see!
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
PostPosted: Fri Jun 08, 2012 3:31 pm
 Reply with quote
shailesh.pawar wrote:
The unloaded data in dataset is

hopelessly corrupted in your post by your refusal to use Code tags.

Browse or edit the data set, and use the HEX command to display hexadecimal values -- and make it apparent what the record lengths are. Copy and paste the display using Code tags to preserve alignment.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
PostPosted: Fri Jun 08, 2012 3:40 pm
 Reply with quote
Quote:
X2X POSITION(11:*) VARCHAR
Should not you be providing some numerical value instead of '*'?
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
PostPosted: Fri Jun 08, 2012 3:48 pm
 Reply with quote
Shailesh- have moved the thread to DB2 part of the Forum and added BBcode tags in your post, please make use of them, when needed.
Back to top
View user's profile Send private message
shailesh.pawar

New User


Joined: 18 May 2012
Posts: 11
Location: India
PostPosted: Fri Jun 08, 2012 3:54 pm
 Reply with quote
Thanks Anuj !!! Below is the unloaded dataset from Db2 table :-
Code:
=COLS> ----+----1----+----2----+----3----+----4----+----5----
****** ***************************** Top of Data *************
000001  ÙSPD         SPD                                     
000002  Ù02          MOF                                     
000003  Ù03          RATES                                   
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
PostPosted: Fri Jun 08, 2012 4:12 pm
 Reply with quote
shailesh.pawar wrote:
Thanks Anuj !!! Below is the unloaded dataset from Db2 table :-
Code:
=COLS> ----+----1----+----2----+----3----+----4----+----5----
****** ***************************** Top of Data *************
000001  ÙSPD         SPD                                     
000002  Ù02          MOF                                     
000003  Ù03          RATES                                   

Since you're too scared to display the data in hexadecimal, you can't be helped.
Back to top
View user's profile Send private message
shailesh.pawar

New User


Joined: 18 May 2012
Posts: 11
Location: India
PostPosted: Fri Jun 08, 2012 4:17 pm
 Reply with quote
Following is the data in Hex format :-

Code:
 000001  ÙSPD         SPD                                     
        0FEDC444444400EDC4444444444444444444444444444444444444
        1D2740000000032740000000000000000000000000000000000000
 -------------------------------------------------------------
 000002  Ù02          MOF                                     
        0FFF4444444400DDC4444444444444444444444444444444444444
        1D0200000000034660000000000000000000000000000000000000
 -------------------------------------------------------------
 000003  Ù03          RATES                                   
        0FFF4444444400DCECE44444444444444444444444444444444444
        1D0300000000059135200000000000000000000000000000000000
 -------------------------------------------------------------


with columns is appears as shown below :-

Code:
 =COLS> ----+----1----+----2----+----3----+----4----+----5----
 ****** ***************************** Top of Data *************
 000001  ÙSPD         SPD                                     
        0FEDC444444400EDC4444444444444444444444444444444444444
        1D2740000000032740000000000000000000000000000000000000
 --------------------------------------------------------------
 000002  Ù02          MOF                                     
        0FFF4444444400DDC4444444444444444444444444444444444444
        1D0200000000034660000000000000000000000000000000000000
 --------------------------------------------------------------
 000003  Ù03          RATES                                   
        0FFF4444444400DCECE44444444444444444444444444444444444
        1D0300000000059135200000000000000000000000000000000000
 --------------------------------------------------------------
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
PostPosted: Fri Jun 08, 2012 4:27 pm
 Reply with quote
shailesh.pawar wrote:
Following is the data in Hex format :-

Code:
 000001  ÙSPD         SPD                                     
        0FEDC444444400EDC4444444444444444444444444444444444444
        1D2740000000032740000000000000000000000000000000000000
 -------------------------------------------------------------
 000002  Ù02          MOF                                     
        0FFF4444444400DDC4444444444444444444444444444444444444
        1D0200000000034660000000000000000000000000000000000000
 -------------------------------------------------------------
 000003  Ù03          RATES                                   
        0FFF4444444400DCECE44444444444444444444444444444444444
        1D0300000000059135200000000000000000000000000000000000
 -------------------------------------------------------------


with columns is appears as shown below :-

Code:
 =COLS> ----+----1----+----2----+----3----+----4----+----5----
 ****** ***************************** Top of Data *************
 000001  ÙSPD         SPD                                     
        0FEDC444444400EDC4444444444444444444444444444444444444
        1D2740000000032740000000000000000000000000000000000000
 --------------------------------------------------------------
 000002  Ù02          MOF                                     
        0FFF4444444400DDC4444444444444444444444444444444444444
        1D0200000000034660000000000000000000000000000000000000
 --------------------------------------------------------------
 000003  Ù03          RATES                                   
        0FFF4444444400DCECE44444444444444444444444444444444444
        1D0300000000059135200000000000000000000000000000000000
 --------------------------------------------------------------

So in fact X2X does not begin in column 11, but in column 13?
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK
PostPosted: Fri Jun 08, 2012 4:30 pm
 Reply with quote
Hello Anuj,

Apparently we can use '*' to control the position/location of the start and end columns of the fields. For VARCHAR the 'end' field is ignored. I tried few variations using '*' and it worked. Correct me if I am wrong.

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.ugref/src/tpc/db2z_loadsyntax.htm
Back to top
View user's profile Send private message
shailesh.pawar

New User


Joined: 18 May 2012
Posts: 11
Location: India
PostPosted: Fri Jun 08, 2012 4:34 pm
 Reply with quote
HI Akatsukami ,

That is what the confusion is !! I am not able to understand , what position should be given !! Does this mean that , we have to manually check for column positions by viewing hex format ??
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
PostPosted: Fri Jun 08, 2012 5:00 pm
 Reply with quote
shailesh.pawar wrote:
HI Akatsukami ,

That is what the confusion is !! I am not able to understand , what position should be given !! Does this mean that , we have to manually check for column positions by viewing hex format ??

I would suggest that the problem lies in a misunderstanding of the original table. Note that in all three records positions 1-2 are 01FD -- possibly, although not certainly, a SMALLINT column. X1X therefore really occupies positions 3-12, with X2X beginning at position 13 (the length descriptor). From inspection (which is suggestive, but not conclusive) the data set appears to be RECFM=FB, LRECL=53 -- not what I'd expect. Either the table has a significantly different layout from your control cards, or the data were created by some process other than a DB2 unload.
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 Copy only TEXT or String from a record SYNCSORT 4
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts SORT on detail record, then repeat he... DFSORT/ICETOOL 3
No new posts how to complete the end of a record w... DFSORT/ICETOOL 2
No new posts Sort based on the record type DFSORT/ICETOOL 1
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us