GROUP BY and ORDER BY for IMS DB?

[RaOne]

Hi,

Do we have something similar in IMS like DB2's GROUP BY and ORDER BY for IMS DB?

[nicc]

No. You have to program it yourself.

[Anuj Dhawan]

nicc is correct. GROUP BY and ORDER BY works on columns and rows while there are no column and rows in IMS. You might use GU, GN but none can be termed as equivalent of these.

[RaOne]

Then if I have to something which needs to sort the result on a given value how will I do it in IMS commands?

[nicc]

You cnnot becuse there is no IMS command to do such a thing. You have to either pass the data to the next step in the job to sort it or invoke sort from your program or write your own sort routine within your program.

An alternative, if your= have DB2 available to you, is to load the data into a temporary DB2 table as you extract it from the IMS database and then use SQL facilities to read the data back from the DB2 table.

[RaOne]

Thanks nicc.

I shall prefer using my own sort routine as getting values from IMS and loading to DB2 would be very expensive than the work required. Thanks for your valuable inputs.

[Anuj Dhawan]

IMS stores the data in segments. The smallest unit which you can get from the IMS database is a segment. Segments have fields, defined for your system. If you want to perform some operation on the field, the only option you are left with is to play with fields... in other words, and as far this thread is concerned, you'll have to write your own sort routine.


An after thought: There is anEXEC DLI interface available for IMS, I have not checked the details but it should also work on the segment level but it might worth checking if it can allow you to have something similar like EXEC SQL ... ORDER BY; but it's highly unlikely.

[RaOne]

IMS stores the data in segments. The smallest unit which you can get from the IMS database is a segment. Segments have fields, defined for your system. If you want to perform some operation on the field, the only option you are left with is to play with fields... in other words, and as far this thread is concerned, you'll have to write your own sort routine.


An after thought: There is anEXEC DLI interface available for IMS, I have not checked the details but it should also work on the segment level but it might worth checking if it can allow you to have something similar like EXEC SQL ... ORDER BY; but it's highly unlikely.

Thanks Anuj. I could find an equivalent of ORDER BY.

[Anuj Dhawan]

IMS stores the data in segments. The smallest unit which you can get from the IMS database is a segment. Segments have fields, defined for your system. If you want to perform some operation on the field, the only option you are left with is to play with fields... in other words, and as far this thread is concerned, you'll have to write your own sort routine.


An after thought: There is anEXEC DLI interface available for IMS, I have not checked the details but it should also work on the segment level but it might worth checking if it can allow you to have something similar like EXEC SQL ... ORDER BY; but it's highly unlikely.

Thanks Anuj. I could find an equivalent of ORDER BY.

That's great!. Can you please share a link to the reference you might have?

[RaOne]

There is a mistake, I wanted to say NOT. I could NOT find an equivalent of ORDER BY.