MVSFORUMS.com

hisabarinath

My requirement is, some valid values are added in the Child table. Based on the input data, we need to convert the corresponding matching valid values from the Column CC2. And the converted values will be write into another report.

For eg: If CC1 is 1, then the value should convert to A,B and C
If CC1 is 4,then the value should convert to A,B and D like that.

dbzTHEdinosauer · Posted: Thu Apr 09, 2009 6:42 am Post subject:

since this appears to be a problem during INSERT or UPDATE, suggest you look at DB2 triggers as a possible alternative:
http://www.redbooks.ibm.com/redbooks/pdfs/sg246300.pdf
_________________
Dick Brenholtz
American living in Varel, Germany

hisabarinath · Posted: Thu Apr 09, 2009 8:42 am Post subject:

So, you mean that we can NOT achive thru
1. Load into a COBOL Array (like the matrix example)
2. Then read from the COBOL array to convert the corresponding matching value?

You mean that appears to be problem during Inert / update mean... Inserting into the Target Db2 table or Inserting into the Matrix (2 dimentision) table???

I'm thinking if we load into corresponding 2 dimensional array, then we can read the CC1 and get the matching CC2 values then will insert into the Target table? But my only concern is how to load these uneven values into the 2 dimentional array?
Let me know, if my requirements are NOT clear Question

hisabarinath · Posted: Thu Apr 09, 2009 8:49 am Post subject:

Here Target Db2 table in the senese Target Report ie., where the converted corresponding valid values are written.

My only problem, is how we do the first 2 Column from the ChildTB (CC1 and CC2) will be loaded into the COBOL Arrays. If i get the clue on this, i have partially completed the requirement.

dbzTHEdinosauer · Posted: Thu Apr 09, 2009 9:50 am Post subject:

INSERT and UPDATE are db2 sql, has nothing to do with your cobol internal table.

your explanations so far still leave me without a clue.

Only thing that I know is that you are trying to modify DB2 data after you have INSERTed/UPDATEd columns of that table.

Your solution seems to rest on a cobol internal table which you want to build.

I suggested db2 Triggers as a way to modify this data as it is INSERTed/UPDATEd since you apparently do not want to modify the application code that accomplishes this.

If you want help on designing your cobol program and the cobol internal table,
we will require a little more info on what the DB2 table problem is, and
what you have to change/fix, and
why.
_________________
Dick Brenholtz
American living in Varel, Germany

jsharon1248 · Posted: Thu Apr 09, 2009 10:25 am Post subject:

I read this earlier before having any caffeine and didn't understand what you're trying to do. Now that I've had my caffeine, I'm more confused. You need to take your time, and explain the requirements better. You talk about a conversion, but I can't understand what is the input to the conversion, and what is the output. You're talking about DB2 tables and COBOL working storage tables and I can't tell when you're talking about one or the other. You also mention opening cursors multiple times and I can't figure that out either. Please provide a better explanation of the requirements along with an example of the input and the expected results.

kolusu · Posted: Thu Apr 09, 2009 10:35 am Post subject:

hisabarinath,

You really need to work on how to express your requirements. I read it at least 5 times but I really cannot map as to what your requirement is. Remember that we cannot see your data nor your design spec to understand what you are trying to do. Post detailed information on what you're trying to accomplish. Do not make people guess what you mean. This will give you a much better chance of getting a good answer to your question.

Kolusu

hisabarinath · Posted: Thu Apr 09, 2009 10:39 am Post subject:

dbzTHEdinosauer,

Since the trigger is used to rest the db2 values, we dont want to change the Db2 data.Your suggestion is by using trigger need to modify the DB2 data, we are NOT looking for modifing my Db2 table, provided we are planning to prepare a report. ie., we are encripting the data from 1 to A,B and C and 2 to A and C. If CC1 = 1 then in the report we have to take A,B and C instead of 1. If CC1 = 2, then the report should prepare with rest of the records along with A and C from CC2 instead of CC1's 1. and If CC1=3, then we have to prepare rest of the records with only C. Since CC1 and CC2 are 2 byte Alphanumeric.

In this case, each time we are openings this ChildTB then fetch those recods and map the corresponding Column of CC2 in the report. Instead of Muliti-time open and close this Db2 table, we are planning to load the ChildTB (db2 data) into a COBOL internal table. In this case, i dont want do more I-O on the ChildTB right? because current Program logic is Opening multiple time the Db2 table and do more I-O operation, it is time consuming.

In this case how can i build the ChildTB Db2 data into a COBOL Internal Table? Let me know if still my requirement is NOT clear?

hisabarinath · Posted: Thu Apr 09, 2009 11:00 am Post subject:

Thanks every one,

I will go other way arround, i have only 2 column (1 is CC1 and 2 nd is CC2) from the above table. I need to prepare a report base on the Input data (CC1) should map with corresponding column of (CC2) to get the value.

IF CC1 value is 1 then i want to write a report like below, (Instead of 1 replace A, B and C)
XX1 A YYYYYY
XX1 B YYYYYY
XX1 C YYYYYY
IF CC1 value is 2 then i want to write a report like below, (Instead of 2 replace A and C)
XX2 A LLLLLLL
XX2 C LLLLLLL
IF CC1 value is 3 then i want to write a report like below, (Instead of 3 replace only C)
XX3 C RRRRR
IF CC1 value is 4 then i want to write a report like below, (Instead of 4 replace A, B and D)
XX4 A MMMMM
XX4 B MMMMM
XX4 D MMMMM
That is mapping the corresponding value to get the report. In this case, i have declared a Cursor like below
Declare Cur_Valvalue CURSOR for
SELECT CC2 FROM CHILDTB
WHERE CC1 = :WS-INPUT-DATA

By using this mulitiple Db2 Operation, i'm getting result but it is a timeconsuming. I'm doing lot of Cursor Open and close operation against the Cur_Valvalue. I dont want to do this, due to taking long time.

So, Please give me your valuable suggestion.

hisabarinath · Posted: Thu Apr 09, 2009 1:04 pm Post subject:

Kolusu - Did the above requirement (Last posting) make sense? I'm still confusing.. that we can use with the DB2 cursor or can load into a Internal Table? Thanks for your time.

Can any one tell me if my last Posting is NOT that much informative?

kolusu · Posted: Thu Apr 09, 2009 1:39 pm Post subject:

hisabarinath,

I have no idea as to what the requirement is anymore. May be I am not that bright to understand a complicated requirement. Sorry

Kolusu

hisabarinath · Posted: Thu Apr 09, 2009 2:17 pm Post subject:

Kolsu - don't say like that. You helped so many developers with out expecting anything. If you say like this, it means, i'm NOT that good in expressing the requirement. You are really great guy by helping us. I feel very bad when genius (you) say 'sorry' Shall i go for new Post?

Let me know where i'm NOT clear?

dbzTHEdinosauer · Posted: Thu Apr 09, 2009 3:29 pm Post subject:

hisabarinath,

no your requirements are not clear - to me - or Kolusu.
(by the way, try to spell his name correctly.)

have you thought of dumping the table to a qsam file,
using DFSORT (Kolusu and Frank can help you there)
or syncsort (many good syncsorters here, as well as Alissa)
to sort he qsam and build a report from that?

Your desire to solve this thru via a cobol internal table and using SEARCH,
could very possibly be solved via the available data manipulation and computation abilities of both the products.
I have seen some really clever and unexpected stuff come out of sort.

you really need to explain what it is that you want.
The explanations you have provided thus far do not meet the goal of communicating your expectations.

try again: explain what data you have, and what you want.
_________________
Dick Brenholtz
American living in Varel, Germany

Terry_Heinze · Posted: Thu Apr 09, 2009 6:20 pm Post subject:

Is CC1 some numeric value between 1 and 10 million? Is CC2 a 1-char field on your DB2 table? Is there an index on CC1? If so, you don't need a 2 dimensional table (array); only a 1 dimensional one, and yes you can load CHILDTB into your COBOL table. (Unless I'm misunderstanding something).
_________________
....Terry

jsharon1248 · Posted: Fri Apr 10, 2009 7:53 am Post subject:

I dusted off the old crystal ball here. It appears that you're just doing a lookup with a single value that could result in multiple records being written to your report. If that's true, you have two options. As I think you're trying to say, you load your search arguments and results into a 2 dimensional COBOL WS table, and then just use a SEARCH to do the lookup. Then write a record for each non-space value in that row. You should also examine the possibility of modifying the process. I'll bet that your input is generated from a DB2 table. You have the opportunity to add a join to the 'child' table you referenced, and then DB2 will do all the work for you. That would be a better solution than writing your 'child' DB2 table into a 2 dimensional COBOL WS table and coding a SEARCH. Let DB2 do what DB2 does best.