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
 

 

Query regarding the DLET and REPL calls in IMS DB
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> IMS DB/DC
View previous topic :: :: View next topic  
Author Message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Tue Jan 19, 2010 6:09 pm    Post subject: Query regarding the DLET and REPL calls in IMS DB
Reply with quote

Hi All,
I have a requirement where in I have to replace a field in child segment at level1 (Root being considered at level 0). There are multiple occurences of this child segment and based on certain criteria I am filtering out a segment (Issuing a GNP call to read all the occurences). Once I find the segment that needs to be deleted, I store the key of the same and issue a GHU call with qualified SSA to root as well as child segment. Then I issue a CALL-REPL and replace a field in child segment. This program is working fine for me, but as per my knowledge of IMS, I should be getting a status code of AJ on this. This is because I am issuing a qualified SSA before a CALL-REPL. Please advise me on this.
Back to top
View user's profile Send private message

Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Tue Jan 19, 2010 6:39 pm    Post subject:
Reply with quote

What is the selection criteria for your child segment? Is there a reason that you need the parent? Why are you holding the parent? Why not just issue an unqualified GHN on the child segments with the root SSA qualified?

Quote:
I should be getting a status code of AJ on this. This is because I am issuing a qualified SSA before a CALL-REPL. Please advise me on this.


WHAT???
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Jan 19, 2010 9:17 pm    Post subject:
Reply with quote

TAPAS PATEL wrote:
I have to replace a field in child segment at level1
TAPAS PATEL wrote:
Once I find the segment that needs to be deleted
These two statements, from your post, have sigificant differences. If I say they are drastically different to each other, 'am, for sure, not being hyperbolic.

And I believe, you got the "sound" of "what" from Sandy - please tell us, why (any otyher reason, what you tell us does not make sense and AJ, with a given description, is a distant possibility) do you think you should get an AJ?
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Thu Jan 21, 2010 9:10 am    Post subject: These two statements, from your post, have sigificant differ
Reply with quote

Hi Anuj,
Yes the two statements are drastically different. I am sorry its my bad, I was trying to do some research on updating of IMS segment and as a result of which I misused delete in place of replace. Its replace that I am trying to do.

Hi Sandy,
The selection criteria for screening the child segment is that I am trying to compare a field (which is not the key field of child segment) with a value that I extract from an input file. If the value matches then I do a replace. This replace is not on the field that I used for comparing, its another field. I hope I was able to answer your first part of the question, Please let me know if I am not clear.

Coming to the second part, There is no special reason to HOLD the parent. I think the option of GHN with root SSA qualified is correct.

And at the end, regarding the status code AJ. I will put forward my understanding please correct it if its wrong. As per my understanding for database calls which involve operations like replace or delete, If there is a qualified SSA issued for this call then the status of AJ is issued. To illustrate my understanding I have given the below example.

|-Item Segment (Key is Item code) -> Root
|-|
|-|-Supplier Segment (Key is Supplier code) -> Child



If there is a requirement to replace the address field of Supplier #sup1234 for Item code #9999. Then if I issue a GHU call with qualified SSA for Item segment and qualified SSA for supplier segment and then issue a REPL call then the status code of AJ will be thrown. As per Quick ref it says that

"A DLET call has multiple or qualified SSAs. "
"A REPL call has qualified SSAs. "

Please let me know if my understanding is correct or not. I tried to program this and execute, and it got executed fine. So I just need to clear my understanding on this.
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Thu Jan 21, 2010 6:02 pm    Post subject:
Reply with quote

As I am understanding your requirement, you have no reason to even retrieve the parent - correct? IMS needs to run efficiently. If you have no need for a segment, why read it? As a matter of fact, I am looking at excessive calls.

What is the format of the input file? You know that you can sequentially read thru all child parents in the database with an unqualified SSA using a GHN for just that segment. If the criteria matches for a REPL then you can issue the replace and continue on your way. No need for excessive calls. If you do indeed need the key of the parent segment, it is in the key feedback area of your PCB. (I am having problems with my PC, so I am writing this quickly before it quits again.)

So, you have an input file and what is the format?
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Fri Jan 22, 2010 11:36 am    Post subject: So, you have an input file and what is the format?
Reply with quote

Hi Sandy,
The input file has two fields, Field1 and Field2. There are two databases that I am dealing with here, namely DB1 and DB2. Please find the structures of the two below:

DB1:

Root DB1 (Key - RDB1)
|-Child DB1 (Key - CDB1)


DB2:

Root DB2 (Key - RDB2)
|-Child DB2 (Key - CDB2)


Properties:

1) The Key of Child DB1 (i.e. CDB1) is the root key for DB2 database (i.e. RDB2).

2) There are more than one occurence of Child DB1 segment of DB1 database.

3) The second field of input file i.e. Field 2 is one of the fields in Child DB2 segment of DB2 database. Note: This is not the key field of this segment.

4) There is a unique occurence of Child DB2 segment in DB2 database.


Requirement:

A flag in Child DB1 segment needs to be updated with the Input fields Field1 and Field2.

Local variables:

1) A Match switch is declared to capture the match between the input file field2 and value of field in Child DB2 segment.

2) A work variable to store the key of CDB1.

Process Followed:

1) Read Input file.

2) Using the Field1 issuing a GET-UNIQUE call to DB1 database.

3) Using GNP call with unqualified SSA to CDB1 segment, read each segment Child DB1 until the match switch is on or status code GB is encountered.

4) Using the Key of Child DB1 (i.e. CDB1), issue a GET-UNIQUE call to DB2 with qualified SSA to root and unqualified SSA to the Child DB2 segment.

5) Compare the Input file field2 with the field value in Child DB2 segment.

6) If the value match flag a switch.

7) Using the key of CDB1 stored in local variable and Input field1 issue a GHU call with qualified SSA to both RDB1 and CDB1.

8) Issue a CALL-REPL and replace the flag.

Please let me know if the process is the most efficient one that can be used in this scenario and Also will I get a status code of AJ when I do a REPL on CDB1.
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Fri Jan 22, 2010 6:12 pm    Post subject:
Reply with quote

Read input file until either end-of-this-file or end-of-datbase1.

Root DB1 (Key - RDB1 - load key from Field1 input file - GU)

1.) What if GE? Create error report?

2.) If found (spaces) use qualified ROOT SSA and unqualified CHILD SSA and perform GHN routine until "GE" or "GB". Hopefully your input file has been externally sorted to be in RDB1 key sequence. In that case, if you get a "GB", you are done. If you still have records on your input file, you have a problem and should generate an error report listing the records still on this file. If "GE" continue with your processing. If spaces - and "Match-switch" (?), then continue with next step.

3.) Use the key from child segment 1 to load qualified root SSA to database 2.

This is where you lost me. WHENEVER you have child segments, you need to plan for multiples. It is a child segment specifically for that reason. If there was never to be a reason for a child segment, that data would be stored on the root.
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Mon Jan 25, 2010 10:06 am    Post subject: If spaces - and "Match-switch" (?)
Reply with quote

Hi Sandy,
Thanks for the reply. I understand the process replied by you. Just to reiterate my understanding, we are replacing the field if the status code is spaces and swith matches. In the program coded by me I am again trying to issue a GHU with qualified SSA to root and child and then replacing the field. This is not necessary.

I am clear about the process that has been suggested by you. But I am still not clear about the second part of my query i.e. about the status AJ. As per my understanding the status AJ is issued when we are issuing a qualified SSA to the segment before doing a replace or delete to that segment. Please let me know if this is correct. If yes, the how can we replace a field in root segment?
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Mon Jan 25, 2010 6:56 pm    Post subject:
Reply with quote

Quote:
But I am still not clear about the second part of my query i.e. about the status AJ. As per my understanding the status AJ is issued when we are issuing a qualified SSA to the segment before doing a replace or delete to that segment.


YOU WILL NOT GET AN AJ - if you get any other status code issuing the calls that will have and will go thru, you should abend your program. You need to always plan for which status codes are possible - spaces, GE, GB, and anything unexpected should cause an abend.

Can you please go thru what segments are getting selectively replaced and what is the role of your second database. Are you replacing both the root and the child segments in DB1? If you are, then you can replace the child segment - you are holding it with your GHN. You have already lost your position on the root, so do a qualified GHU and then your replace against the root.
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Mon Jan 25, 2010 9:49 pm    Post subject:
Reply with quote

Here's an idea. Let's approach like this:

Data Base ONE:
|-Item Segment (Key is Item code) -> Root - retrieved GU based on field from input file - is replaced based on? Looks like it is updated if any child segments were updated. Use this qualified SSA and unqualifed child SSA to retrieve child segments until end-of-database or no-more-child-segments - if switch is set - retrieve the root again and replace - you need to retrieve the root again because you have lost POSITION. You are always positioned at your last successful call.

|-|-Supplier Segment (Key is Supplier code) -> Child - retrieved based on a field match from input file - if match - segment is updated and replaced - switch is set to update root - continue retrieving child segments under this qualifed ROOT SSA until "GB" or "GE"

My big question is Data Base TWO logic? Is it the same process?
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Wed Jan 27, 2010 10:13 am    Post subject: My big question is Data Base TWO logic? Is it the same proce
Reply with quote

Hi Sandy,
Data Base TWO is just to identify the child segment of DB1. This has not updations being perfomed on it. Please let me know if I am not clear.
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Wed Jan 27, 2010 4:12 pm    Post subject:
Reply with quote

You are not clear.
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Wed Jan 27, 2010 5:53 pm    Post subject: You are not clear.
Reply with quote

As per my understanding of your question, you are referring to the logic of Data Base 2 (i.e. DB2). If this is correct please find my explanation for logic of DB2 below.

Structure of databases:

Database 1:
|-Item Segment (Key is Item code)
|-|-Supplier Segment (Key is Supplier code) -> (Key - Supplier No)

Database 2:
|-Supplier Root -> (Key - Supplier No)
|-|-Supplier details -> (Key - Supplier Address)

Example:

Input file data -

Item Code - 1234
Phone No - 9898989898

Database 1:

|-Item Segment (Item code = 1234)
|-|-Supplier Segment (Supplier code - 9999, Field ABC - N)
|-|-Supplier Segment (Supplier code - 7777, Field ABC - N)

Database 2:

|-Supplier Root -> (Supplier No - 7777)
|-|-Supplier details -> (Supplier Address - XYZ, Ph No - 9898989898)


In the above illustration, Field ABC in Supplier segment of DB1 needs to be made as Y. The details given are Item code and Phone No. We read the Supplier segment of DB1 one by one and find out the value of Phone no in DB2 and if matches then that segment is replaced with value of field ABC as 'Y'. Then the next record is read, and so on till End of file.
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Wed Jan 27, 2010 6:12 pm    Post subject:
Reply with quote

Ok - so a supplier has multiple addresses - correct? So, you need to get the supplier root and then read the child segments until you find a match on phone number - correct?
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Wed Jan 27, 2010 6:42 pm    Post subject:
Reply with quote

Reading back thru all of these posts, I think that I need to explain something. You cannot replace 2 segments with one path call. However, when you delete a parent segment - ALL children are automatically deleted - all the way down the chain - if you have many levels and a million child segments, they all go bye-bye.

Any call with an "H" in it is a HOLD call. Once you read beyond that segment you are no longer holding it. You always need a HOLD to perform any update to an existing segment - including a delete.

You can do a PATH insert, but we'll discuss that if you need it.

Your last example of the problem was WONDERFUL. Please use examples like this when presenting a problem. It is sometimes difficult to help without them.
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Thu Jan 28, 2010 6:57 pm    Post subject: Once you read beyond that segment you are no longer holding
Reply with quote

If I am holding a segment of PCB DB1 (i.e. Item database) and then making a call to PCB DB2 (i.e. Supplier database) and then coming back and updating the segment that was on HOLD of PCB DB1. In this scenario also will there be a problem, because I am not realising the HOLD on segment of PCB1? Please let me know if I am not clear.
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Thu Jan 28, 2010 7:17 pm    Post subject:
Reply with quote

No problem. You have two different POSITIONS in two different databases. As a matter of fact, you could have multiple PCBs for the same database, but that is a story for another day.
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Fri Jan 29, 2010 12:53 pm    Post subject: No problem. You have two different POSITIONS in two differen
Reply with quote

Thanks for the reply...Sandy!!!
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Jan 29, 2010 6:45 pm    Post subject:
Reply with quote

SO, did you get it working?
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Mon Feb 01, 2010 7:08 pm    Post subject: SO, did you get it working?
Reply with quote

Hi Anuj,
Yes its working for me now. The good thing is that I coded the program to accomplish my job more effeciently now, Thanks to Sandy for this. icon_smile.gif
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 -> IMS DB/DC All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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