Loading the DB2 tables using IMS database unloads

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

Hi Guys,

I have a requirement wherein I have to load the DB2 tables using the unloads of IMS databases. I am not sure, how its done. I would like to know if there are any utilities to perform this functionality which can be used in the JCL.

Please let me know incase more information is needed.

Akatsukami · Posted: Mon Nov 19, 2012 4:26 pm

You know, of course, that IMS and DB2 are completely different in terms of organization (and other things, but that's the most immediate problem). Assuming that your DBAs have the tables and the RI set up correctly, you can use your sort product to split up the IMS unload into segment types and load using whatever DB2 utilities you have to hand (IBM or BMC are the two most widely used).

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

Unfortunately, the requirement is to create DB2 tables first (that should be done by me as well). Once, these tables are created, the unloading of the IMS databases should be done and then, this data needs to be loaded in these DB2 tables.

And moreover, I do not have any tools that split up the IMS unload into segment types.

I am not aware of the utilities that perform the loading of DB2 tables using the IMS unloads. That's what my query is actually about.

Akatsukami · Posted: Mon Nov 19, 2012 5:48 pm

Well, I'm not a DBA, although I sometimes perform low-level DBA tasks. There are better people on this board to advise you, particularly GuyC; until they come along, however, I'll try to muddle through.

Familiarize yourself with the LOAD utility. Note that it permits you to load from sources that are not laid out identically to the table to which the data are to be loaded.

All the information to reload an IMS data base is in the unload itself. I don't have my notes here at home, but essentially there are headers that name the segments and map them to sequence numbers and hierarchical levels. Then the data records are prefixed with those sequence numbers and other information that is (probably) not relevant to your task.

So: take the unload and use whatever sort product (DFSORT, Syncsort, etc.) your shop has to strip off the headers and prefixes and write the segments of each type to a different data set. Create the tables, each with the same layout as a segment. Use the DB2 LOAD utility to load each data set to a table.

Gnanas N · Posted: Mon Nov 19, 2012 7:28 pm

Is this a part of IMS DB to DB2 migration?

Please do googling on "IMS DB to DB2 migration" for more information like migration approach and tools.

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

Hi Gnanas,

This is not a part of IMS DB to DB2 migration.

I just got a requirement to perform these activities, as part of regular work. I would be happy, if I could know the procedure to do this.

1. How to load the DB2 tables using IMS database unloads. or rather, is it possible to load a DB2 table using an IMS unload, or the unload of the database has to be converted into a flat file and then load into DB2 table.

enrico-sorichetti · Posted: Tue Nov 20, 2012 2:46 pm

an IMS database unload is understood from the reload counterpart

to load a DB2 table You must transform the data to something that DB2 understands.

as usual You are the innocent victim of an IDIOT
who assigned You a task without checking about Your knowledge about it
and without providing You the proper <documentation>

You should have handy ...
the DB2 tables DDL ( which fortunately gives You the names of the colums)
ALL the IMS segments Layout ( copybooks )
ALL the relations of what goes where
IMS segment - DB2 table / IMS field - DB2 column relations

and after that start writing
- DFSORT control cards
- COBOL programs
to remap <things> and build all the files needed for the DB2 reload

anyway I cannot do anything but CONFIRM that
the person who invented and assigned to You the task are ..
morons/idiots/... add as many derogatory adjectives as You like

(*) I said invented ... not PLANNED.
I see very little planning here

as You see, given the complexity of the task the chances of getting conclusive help on a forum are pretty slim

also the task of converting IMS databases to DB2 tables is not something
that can be simply assigned to a person,
with a GO BOY we are all behind You (*)

(*) to to have certain people behind is pretty dangerous for Your well being

Anuj Dhawan · Posted: Tue Nov 20, 2012 6:58 pm

Well - it's not that straight as you state it, Kunal. There is no direct tool to do IMS to DB2 load unless, you've some custom made tool available for the given structures of DBs at your shop.

At minimum - you've to have the DDL of DB2 table and the copybook structure of IMS segments, as Enrico says. And then, as a analyst, you need to write a bit of code to make the unload compatible for DB2 load.