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

Automation for table insert


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
george16

New User


Joined: 08 Jul 2010
Posts: 11
Location: chennai

PostPosted: Sun Jul 11, 2010 10:54 pm
Reply with quote

Hi, I have a requirement thru COBOL-db2. In a table(BLACT) having a number of rows, for a particular column (BLACT_F ) , if a row has the value '%OTNR' then in order to insert the two rows 'VSPDOTNR' and 'VDADOTNR', what should be done?
Please note that the two values should be passed outside the program for "Automation purpose".

For ex.
Blact_f Blact_d logn_f
------------------------------------------
XRFDOTNR VA BUS
ERTROTNR WA RES
SWAROTNR DC RES

we NEED TO CHANGE:

Blact_f Blact_d logn_f
------------------------------------------
XRFDOTNR VA BUS
VSPDOTNR VA BUS
VDADOTNR VA BUS
ERTROTNR WA RES
VSPDOTNR WA RES
VDADOTNR WA RES
SWAROTNR DC RES
VSPDOTNR DC RES
VDADOTNR DC RES

kindly help me with this program....
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sun Jul 11, 2010 11:06 pm
Reply with quote

Hello and welcome to the forum,

A rather bad start - you obviously did not read the forum rules or look at any of the thousands of recent posts asking for something. . . icon_sad.gif

Why in the world did you use "Help!" as a subject? If everyone who wanted help with something did this, there would be no meaning to the forum.

What do you have problems doing? What have you done so far? You need to show what you have done. We will help but will not write your programs for you. If anyone posts a complete "solution" it may be deleted.

To answer your question, read the table and if you find the value insert the rows. . . Values are not "passed outside the program - they are written to files or other tables. One type of "output" file might be jcl to be submitted thereby making the value available outside the program. Takes a little planning but is rather simple. This is done via the internal reader - there are many internal reader topics in the forum. This will also be a good introduction for you to the forum search feature.
Back to top
View user's profile Send private message
george16

New User


Joined: 08 Jul 2010
Posts: 11
Location: chennai

PostPosted: Sun Jul 11, 2010 11:55 pm
Reply with quote

Hi, I have a requirement thru COBOL-db2. In a table(BLACT) having a number of rows, for a particular column (BLACT_F ) , if a row has the value '%OTNR' then in order to insert the two rows 'VSPDOTNR' and 'VDADOTNR', what should be done?
Please note that the two values should be passed outside the program for "Automation purpose".

For ex.
Blact_f Blact_d logn_f
------------------------------------------
XRFDOTNR VA BUS
ERTROTNR WA RES
SWAROTNR DC RES

we NEED TO CHANGE:

Blact_f Blact_d logn_f
------------------------------------------
XRFDOTNR VA BUS
VSPDOTNR VA BUS
VDADOTNR VA BUS
ERTROTNR WA RES
VSPDOTNR WA RES
VDADOTNR WA RES
SWAROTNR DC RES
VSPDOTNR DC RES
VDADOTNR DC RES

kindly help me with this program....
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jul 12, 2010 12:14 am
Reply with quote

Hello,

Repeating what was replied to your first post of this question (which is now locked).

Quote:
What do you have problems doing? What have you done so far? You need to show what you have done. We will help but will not write your programs for you. If anyone posts a complete "solution" it may be deleted.

To answer your question, read the table and if you find the value insert the rows. . . Values are not "passed outside the program - they are written to files or other tables. One type of "output" file might be jcl to be submitted thereby making the value available outside the program. Takes a little planning but is rather simple. This is done via the internal reader - there are many internal reader topics in the forum. This will also be a good introduction for you to the forum search feature.


You probably need to better explain "automation purpose". What does this mean to you for this process? How will this interact with an automation product?
Back to top
View user's profile Send private message
george16

New User


Joined: 08 Jul 2010
Posts: 11
Location: chennai

PostPosted: Mon Jul 12, 2010 12:35 am
Reply with quote

Suppose a program is written, compiled and moved to production. Then whenever a row has to be inserted, I just run the job without touching the program.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jul 12, 2010 12:49 am
Reply with quote

Hello,

In that same program, implement code to submit the "next" process thru the internal reader. . .

Quote:
Then whenever a row has to be inserted, I just run the job without touching the program.
I'm sure this is clear to you, but not to others (i.e. me<g>). You need to explain the concept of "whenever a row has to be inserted". . .

If you intend to add some process that does not yet exist, something has to be "touched". May be only the original program or the original program plus the "new" process.

Possibly there is still something i misunderstand.

If all of this is to get around making a change to the original program to simply insert the rows, that could use some explanaton as well. . .
Back to top
View user's profile Send private message
venkatesh83be

New User


Joined: 10 Aug 2009
Posts: 12
Location: Chennai

PostPosted: Mon Jul 12, 2010 11:32 am
Reply with quote

Hi Dick,

George16 is trying to convey that the program is file driven. It means whatever is there in file (in proper format) will be inserted by existing program itself.

So he wants us to do changes as he has explained in the file before the file gets loaded by the program.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Jul 12, 2010 11:37 am
Reply with quote

whatever George was trying to convey, He was not successful
if He wants help He has to do a better job in explaining...

I looked at his profile, his skills are jcl,cobol,vsam,db2,cics
so I would expect, even with the language barrier, that He should be able to express clearly his requirements

If He cannot... we are all losing time icon_biggrin.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jul 12, 2010 12:22 pm
Reply with quote

Hello,

Quote:
So he wants us to do changes as he has explained in the file before the file gets loaded by the program.
His explanation is not clear and i don't understand what this tells me either. . .

I must be entering what will be a really difficult week icon_smile.gif

If this were my task i would read the file and when i found a value of '%OTNR', i would insert the 2 needed rows. I see no opportunity for "Automation purpose".

So far, no one has posted why this would not work. . .
Back to top
View user's profile Send private message
george16

New User


Joined: 08 Jul 2010
Posts: 11
Location: chennai

PostPosted: Mon Jul 12, 2010 2:12 pm
Reply with quote

i am selecting the table, then in that same table which column having values '%OTNR' then need to insert 2 more rows.

if any posibility. could you pls update the sample logic program...
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Jul 12, 2010 2:19 pm
Reply with quote

this is a help forum, not a do it for me icon_biggrin.gif

tell us please what issues You are facing and somebody will be glad to help

looks like the requirement is a bit more complicated than the one You are describing

what if, for example one of the rows You want to insert is already there ??

what is the percentage of rows being inserted? if it is significant
an unload/process/reload might provide better performance
Back to top
View user's profile Send private message
george16

New User


Joined: 08 Jul 2010
Posts: 11
Location: chennai

PostPosted: Mon Jul 12, 2010 2:36 pm
Reply with quote

I have used cursor,

Perform 1000-fetch-para
thru 1000-exit until end-of-cursor.

inside of 1000-fetch-para :

when 0 perform insert-para.

inside of insert-para:

insert the rows to table.

i tried like this logic , but i have found 120 rows , but in that same table having inseted only 5 rows so that i put that query

but i need to insert 240 rows in that table.

and can i know if any other keys i need to set or..



[/code]
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Jul 12, 2010 2:41 pm
Reply with quote

if the insert was successful for five rows it should be also for the other ones

what happened inserting the sixth one ??
(maybe some duplicate KEY ??? )

but as i said before given the insertion rate an unload/insert/reload
will certainly be a better approach!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jul 12, 2010 7:48 pm
Reply with quote

Hello,

What happened to "automation"?

What are the chances that all but the 5 newly inserted rows were duplicates of already existing rows? What should happen when a row to be inserted already exists?

If i understand what is needed, it is not difficult - once the rules are established. . . This could be as simple as read the rows for the desired value, check to see if the VSPDOTNR value already exists and if not insert one, check to see if the VSADOTNR value already exists and if not insert one. . .
Back to top
View user's profile Send private message
george16

New User


Joined: 08 Jul 2010
Posts: 11
Location: chennai

PostPosted: Tue Jul 13, 2010 5:04 pm
Reply with quote

I have tried two rows inserted, 3 row i got -803 error thats mean index error..
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jul 13, 2010 5:56 pm
Reply with quote

george16 wrote:
i got -803 error thats mean index error..


you need to read more than the first line of an error message.

a -803 means you tried to INSERT a row with a key that already exists in a table with index defined as UNIQUE.

duplicate insert....
you have done a poor job of explaining your requirement.
what should you do if you attempt to INSERT a duplicate?
we can't tell you.
Back to top
View user's profile Send private message
geekram

New User


Joined: 09 May 2010
Posts: 4
Location: Mumbai, India

PostPosted: Tue Jul 13, 2010 11:24 pm
Reply with quote

Hello!

I have one solution for this problem here.

Read the table with the cursor then wherever you find '%OTNR' insert two more rows in the temporary array in your COBOL application as per your requirement.

Then after reading the entire table as above and performing inserts in the COBOL array table then at the end of the program read this array and insert into your actual DB2 table. Wherever you encounter error for the duplicate KEY skip it and continue with the next row i think you don't need to insert that row as it already exists in the DB2 table.

Thanks!!!

Cheers,
Ram. icon_biggrin.gif
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 -> COBOL Programming

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
Search our Forums:

Back to Top