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
 

 

Multi-rows insert and a number of a bad record

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

New User


Joined: 15 Nov 2011
Posts: 4
Location: Belarus

PostPosted: Tue Nov 15, 2011 9:41 pm    Post subject: Multi-rows insert and a number of a bad record
Reply with quote

Hi.
I'm using multi-rows insert in a C-program. The program reads a file, populates SQLDATA arrays and executes insert. Because of bad data in the file DB2 fails insert. Is it possible to get a number of a bad record in the array/file? Thanks in advance.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Nov 15, 2011 9:51 pm    Post subject:
Reply with quote

It even provides the code :Checking the execution of SQL statements by using the GET DIAGNOSTICS statement
Back to top
View user's profile Send private message
Vladimir Mestovski

New User


Joined: 15 Nov 2011
Posts: 4
Location: Belarus

PostPosted: Wed Nov 16, 2011 2:06 pm    Post subject:
Reply with quote

Thank you very much GuyC, the DB2_ROW_NUMBER is exactly what I need.

I'm creating a program that combines almost all features of DSNTEP4, DSNTIAUL, LOAD utility, QMF style of a SELECT report, and more...
Don't know if similar program already exists...Here is an output of the program:
*** SQLMON - Batch SQL Monitor *** (built on Nov 16 2011 02:00:05 by VM)
*** CURRENT TIMESTAMP = 2011-11-16-02.58.16.044467
*** Used parameters:
INSQL(DD:SYSIN)
TAGS()
OUTSQL()
CONNECT TO BYDB2LAB;
*** CONNECT successful, SERVER_NAME='BYDB2LAB'
WRITE CSV FILE DD:DSN1 SELECT * FROM CPEOUTT.TIM_OVG_CLASS WITH UR;
*** Successful WRITE of 134355 record(s), RECFM=FB, LRECL=194
*** CPU time = 1.3602 seconds, TOTAL time = 4 seconds

CONNECT TO BYDB2LABE;
*** CONNECT successful, SERVER_NAME='BYDB2LABE'
DECLARE GLOBAL TEMPORARY TABLE SESSION.TIM_OVG_CLASS
LIKE CPEOUT.TIM_OVG_CLASS;
*** DECLARE successful
*** CPU time = 0.0056 seconds, TOTAL time = 0 seconds
READ CSV FILE DD:DSN1 INSERT INTO SESSION.TIM_OVG_CLASS VALUES(*);
*** VALUES(1.14,16.14,31.10,42.11,54.10,65.11,77.10,88.11,100.10,111.11,123.10,
*** Successful READ & INSERT of 134355 row(s)
*** CPU time = 2.4608 seconds, TOTAL time = 5 seconds
CREATE INDEX SESSION.I1 ON SESSION.TIM_OVG_CLASS (STAGE_PN);
*** CREATE successful
*** CPU time = 0.4951 seconds, TOTAL time = 2 seconds

EXPLAIN SELECT * FROM SESSION.TIM_OVG_CLASS WHERE STAGE_PN LIKE '00%';
+------+-----+------+-----+------+------+-----+------+-----+-------+------------
|PARENT|QUERY| QUERY| | | |USED | | | |
|QBLOCK|BLOCK| BLOCK|RUN |JOIN |ACCESS|INDEX|TABLE |TABLE| TABLE | TABLE
|NO |NO | TYPE |ORDER|METHOD|TYPE |COLS |NUMBER|TYPE | OWNER | NAME
+------+-----+------+-----+------+------+-----+------+-----+-------+------------
| 0 | 1 |SELECT| 1 | 0 | I | 1 | 1 | T |SESSION|TIM_OVG_CLAS
+------+-----+------+-----+------+------+-----+------+-----+-------+------------
*** Successful retrieval of 1 row(s)
*** CPU time = 0.0004 seconds, TOTAL time = 0 seconds
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 compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts Need to write record of PS File in ex... Chandan1993 JCL & VSAM 1 Wed Jun 07, 2017 1:35 am
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm


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