Retrieving highest value of SSA IMS



IBM's hierarchical database management system with a Database Manager (IMS DB) and a Transaction Manager(IMS DC)

Retrieving highest value of SSA IMS

Postby rockstar2020 » Tue Jul 22, 2014 12:53 pm

Hello All,

My particualr IMS db structure looks like this.

PLA00000 Level 1- FIRST 5 BYTES KEY AND remaining - parent
PLA05000 Level 2- FIRST 5 BYTES KEY AND remaining - child

Concentrating on the child segment(PLA05000), it has one primary ssa key and many other secondary ssa keys and one of the ssa is ORDER-ID.
EXAMPLE - Many child segements are available under one parent and My requirement is that I've to retrieve the highest order id avaialble using one IMS call.
*********************************** PLA00000 ***********************************
++++5+++10+++15+++20+++25+++30+++35+++40+++45+++50+++55+++60+++65+++70+++75+++80
PPPPPZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

*********************************** PLA05000 ***********************************
++++5+++10+++15+++20+++25+++30+++35+++40+++45+++50+++55+++60+++65+++70+++75+++80
QQQQQZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ10105ZZZZZZZZZZZZZZZZZZZZZZZZZZZ
RRRRRZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ90101ZZZZZZZZZZZZZZZZZZZZZZZZZZZ
SSSSSZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ20102ZZZZZZZZZZZZZZZZZZZZZZZZZZZ
TTTTTZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ70103ZZZZZZZZZZZZZZZZZZZZZZZZZZZ
UUUUUZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ30101ZZZZZZZZZZZZZZZZZZZZZZZZZZZ


In general PLA05000*L gives the last segment(UUUUU) similarly, in accordance with order-id(position - 50) i need to retrieve 2nd segment(RRRRR) which is
the highest value, using one IMS call.

Otherwise i need to look all the segments one by one to acheive this and that is time and cpu consumption as there could be 1000 of child segments real time.

Highly appreciate any suggestions. Thank you.
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Retrieving highest value of SSA IMS

Postby Ed Goodman » Tue Jul 22, 2014 8:16 pm

I think you're stuck. There is no equivalent to "highest value" in an IMS SSA. You could get into doing some secondary indexing, but I doubt you have the permission to do that.

The idealist in me says to take that order number and move it to another(new) segment, store the order number as a negative value and you can always get the highest number by grabbing the first one.

How are order numbers incremented in your system? It seems weird to me that you you have to find the highest order number for a customer by digging through the customer info. There is probably an order db with the link to the customer in it already.
Ed Goodman
 
Posts: 341
Joined: Thu Feb 24, 2011 12:05 am
Has thanked: 3 times
Been thanked: 17 times

Re: Retrieving highest value of SSA IMS

Postby rockstar2020 » Tue Jul 22, 2014 8:54 pm

Thanks for the reply. By the way ORDER ID is just example i gave and maybe different from real name. You are correct order id is incremented from 1 by 1 for the available child segments. Yes, DB is sorted on primary key and i dont have access to build sec index, also this is just a small requirement feels unnecessary to do so.

I've re-coded the primary and secondary keys of the DB again below. As u can see below, primary is sorted out but not the order-id and it will not be most cases.
---> My requirement is i have to insert another child segment and i have the all the data populated but to write the order-id, for the new segment, i've to know the highest order-id already available and increment by 1. So, i thought i can read the
highest child that has highest order id and increment that value by 1 and use for the next segment.

As info, available data in this particular DB is already written by another program.

*********************************** PLA00000 ***********************************
++++5+++10+++15+++20+++25+++30+++35+++40+++45+++50+++55+++60+++65+++70+++75+++80
PPPPPZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

*********************************** PLA05000 ***********************************
++++5+++10+++15+++20+++25+++30+++35+++40+++45+++50+++55+++60+++65+++70+++75+++80
AAAAAZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ00005ZZZZZZZZZZZZZZZZZZZZZZZZZZZ
BBBBBZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ00001ZZZZZZZZZZZZZZZZZZZZZZZZZZZ
CCCCCZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ00003ZZZZZZZZZZZZZZZZZZZZZZZZZZZ
DDDDDZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ00002ZZZZZZZZZZZZZZZZZZZZZZZZZZZ
EEEEEZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ00004ZZZZZZZZZZZZZZZZZZZZZZZZZZZ


I am welcome for the alternate ways and suggestions as well. Thank you.
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Retrieving highest value of SSA IMS

Postby Ed Goodman » Wed Jul 23, 2014 5:43 pm

So, the way you currently pick the next order number is to find the highest one, then increment it? So all customers will have all the same order numbers?

If this is true, then either add that order number on the parent segment so you can grab it there, or create a new segment with nothing but those types of increments.

It's almost like your IMS schema was created by a person who only knew relational databases.
Ed Goodman
 
Posts: 341
Joined: Thu Feb 24, 2011 12:05 am
Has thanked: 3 times
Been thanked: 17 times

Re: Retrieving highest value of SSA IMS

Postby rockstar2020 » Thu Jul 24, 2014 7:16 am

For example, if PLA05000 key is customer, then order-id is another SSA, so order-id will not be repeated and is unique under same PLA00000 for every PLA05000. Problem is I cannot update any data to parent segment just for this requirement kinda storing highest order id in parent segment and then use it every time. I have the right to just insert a new child with new customer and highest order-id+1.

This is relatively small change and I dont want to imapct the other functionality/fields of the db by modifying or storing the order -id elsewhere to use for this purpose alone.
Thats why i thought if i have a way to find out child segment that is having highest order-id on a single call, i can use the data to increment.
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Retrieving highest value of SSA IMS

Postby Ed Goodman » Thu Jul 24, 2014 6:50 pm

Well, if you really are in such a tight spot, how about...create a fake PLA05000 with a bogus customer number, or an inactive flag or something that makes it not show up anywhere. Then use that bogus one to keep track of the highest number.
Ed Goodman
 
Posts: 341
Joined: Thu Feb 24, 2011 12:05 am
Has thanked: 3 times
Been thanked: 17 times


Return to IMS DB/DC