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
 

 

DB2 Query for inserting new records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
krunalbafna
Warnings : 1

Active User


Joined: 18 Jan 2010
Posts: 143
Location: Pune

PostPosted: Wed Mar 17, 2010 11:26 am    Post subject: DB2 Query for inserting new records
Reply with quote

Hi team,

I have one table with no primary keys.
I have to insert a record in the table but should take care that the data with combination (CUST_NO,SYS_CDE,ACCT_NO) is not present.
any query which will help me before inserting new records and checking its presence too.

Following is the dclgen of the table:
CUST_NO CHAR(10) NOT NULL,
SYS_CDE CHAR(1) NOT NULL,
ACCT_NO CHAR(20) NOT NULL,
APPL_CDE CHAR(8) NOT NULL,
JOBNAME CHAR(8) NOT NULL,
CREA_DTE DATE NOT NULL,
STA_IND CHAR(1) NOT NULL,
COPY_DTE DATE NOT NULL,
REQUESTOR CHAR(30) NOT NULL,
PROJ_NO CHAR(14) NOT NULL,
PROJ_DESC CHAR(60) NOT NULL

Any suggestion please let me know.
Back to top
View user's profile Send private message

raj.tumu

New User


Joined: 21 Oct 2009
Posts: 7
Location: india

PostPosted: Wed Mar 17, 2010 2:54 pm    Post subject:
Reply with quote

Do you want to do it with a program or with a spufi.

if its thru a program go for a fetch on the table with these values in where condition if the values matched do what you want else go for insert..
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Mar 17, 2010 3:03 pm    Post subject:
Reply with quote

Quote:
I have to insert a record in the table but should take care that the data with combination (CUST_NO,SYS_CDE,ACCT_NO) is not present.

What if combination is already present in the table?
Back to top
View user's profile Send private message
krunalbafna
Warnings : 1

Active User


Joined: 18 Jan 2010
Posts: 143
Location: Pune

PostPosted: Wed Mar 17, 2010 3:20 pm    Post subject:
Reply with quote

if it is already present go to next record.


I am using program for the inserting those records
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Wed Mar 17, 2010 6:55 pm    Post subject:
Reply with quote

Hi Krunal

Why don't you post your DB2 questions in the dedicated DB2 forum?

I will suggest that you create an index on CUST_NO,SYS_CDE,ACCT_NO with the unique option. That way you just insert rows, if you get SQLCODE -803 you know that you hit an existing key combination, and it was not inserted
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Mar 17, 2010 7:55 pm    Post subject:
Reply with quote

Please use a more meaningful topic subject next time.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Mar 17, 2010 10:18 pm    Post subject:
Reply with quote

Krunal,

Quote:
should take care that the data with combination(CUST_NO,SYS_CDE,ACCT_NO) is not present


Ask your DBA to create an unique index on the above columns. WAP to read from PS file and INSERT into table. If you get a -ve SQLCODE saying about no two rows can contain duplicate values meaning table already contains those values. So, handle that SQLCODE and procede with next.

Sushanth
Back to top
View user's profile Send private message
manikawnth

New User


Joined: 07 Feb 2007
Posts: 61
Location: Mumbai

PostPosted: Thu Mar 18, 2010 11:30 am    Post subject: @Krunal
Reply with quote

I am very ignorant abt DB2 that i cant give u an answer with a single SQL query.

Query1: Do a SELECT on the table with where clause having the same values to be inserted.
Query2: Execute INSERT ony if previous SQLCODE is +100.


Thanks,
Manikanth
P.S: Do I make sense or Am I poor performing?
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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am


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