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: 193
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 Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Db2 load job abending in SORT mistah kurtz DB2 4 Mon Jun 05, 2017 3:58 pm
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts Copy load modules and alias vasanthz IBM Tools 5 Thu Apr 20, 2017 6:31 am


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