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
 

 

IDENTITY column in LOAD & Insert with multi-select

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

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed May 13, 2009 1:40 pm    Post subject: IDENTITY column in LOAD & Insert with multi-select
Reply with quote

Hi,

I am trying to use CROSS-LOADER feature of DB2 z/OS to load data from production to development, since i have only few rows to load. And this job failed because of table having IDENTITY COLUMN GENERATED ALWAYS.

Code:
LOAD DATA LOG NO RESUME NO REPLACE INCURSOR NSSI
INTO TABLE ASFDDATP.SITTPNSSI                   
FIELD PK_ROW_NUM IS NOT ALLOWED                   


So, i specified IGNOREFIELDS YES in my loadcard, again i got the same error.
Code:
LOAD DATA LOG NO RESUME NO REPLACE INCURSOR NSSI
INTO TABLE ASFDDATP.SITTPNSSI IGNOREFIELDS YES   
FIELD PK_ROW_NUM IS NOT ALLOWED                 

How should i load using CROSS LOADER function if the table to be loaded has IDENTITY COLUMNS ?

So, i tried the next available option. Just an ordinary insert.
Code:
  INSERT INTO ASFDDATP.SITTPNSSI OVERRIDING USER VALUE                         
     SELECT * FROM NLVPROD.ASFDDATP.SITTPNSSI;                                 
I got an
Code:
SQLCODE = -512, ERROR:  STATEMENT REFERENCE TO REMOTE OBJECT IS INVALID

If i comment out the INSERT statment and run the SELECT query
Code:
SELECT * FROM NLVPROD.ASFDDATP.SITTPNSSI;

I am able to get the data.


Am i missing something in LOAD as well the INSERT query ?

Thank You,
Sushanth
Back to top
View user's profile Send private message

Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Fri May 15, 2009 1:41 am    Post subject: Reply to: IDENTITY column in LOAD & Insert with multi-se
Reply with quote

Hi sushanth bobby

How do you defined the cursor NSSI ?
Did you avoid the IDENTITY column while defining it?
I think you can avoid the IDENTITY column while declaring Cursor(Instead of using a SELECT * FROM..) since both tables (prod & dev)having the same structure.

If you are using Load control statement without using Cursor, then you can use the combination of IGENOREFIELDS with dummy fields (DSN_IDENTITY). This will avoid loading IDENTITY columns.

This is only a logical thinking. I never tried it. Try it. Good luck.

Regards
Raghu
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat May 16, 2009 12:04 am    Post subject:
Reply with quote

Raghu,

Your logical thinking is correct about the LOAD. Thank You RAGHU.

The INSERT statement is not working, only when i am using THREE-PART names.

Thank You Very Much,
Sushanth
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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 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