migrating tables from MVS to Windows version of DB2

[Stephen Jenkinson]

Hello.

We are tring to migrate a bunch of table that resides db2 (version 6 I
believe) on a MVS mainframe environment to DB2 UDB version 7.0 on a
windows NT environment.

We are tring to achive this by exporting and importing in the IXF
format, however the PC/IXF format appears to differ from that export
from the mainframe.

Does anyone have any suggestion on how to migrate these tables
accross. There are around 100 tables, some of which have alot of
colunms.

Thanks in advance

[Mark A]

Stephen Jenkinson" <sp.jenkinson@student.qut.edu.au> wrote in message
news:4f7c9ed9.0309111641.543aa4e@posting.google.co m...

> Hello.
>
> We are tring to migrate a bunch of table that resides db2 (version 6 I
> believe) on a MVS mainframe environment to DB2 UDB version 7.0 on a
> windows NT environment.
>
> We are tring to achive this by exporting and importing in the IXF
> format, however the PC/IXF format appears to differ from that export
> from the mainframe.
>
> Does anyone have any suggestion on how to migrate these tables
> accross. There are around 100 tables, some of which have alot of
> colunms.
>
> Thanks in advance

You can create some sequential MVS files with the DB2 sample program
DSNTIAUL (See Appendix D of the Version 6 Utilities Manual) and then
download the files to NT. Make sure you convert packed (comp-3) and binary
(comp) fields to display numeric (maybe with the CAST function on the select
that extract the data). You will need to download the files as MVS text, not
binary, using a program like IBM's Personal Communications that will convert
the data from EBCDIC to ASCII.

Then you can use import/load utility on DB2 for Windows. Obviously, you will
have to create the tables and indexes, etc on DB2 for Windows before-hand.

Just out of curiosity, what did you use to create the IXF extract on the
mainframe?

I realize you may not have much control over this, but it runs a lot better
under Windows 2000.

[Philip Nelson]

Stephen,

If you have DB2 Connect access to mainframe DB2 then you can run the
export command from the UDB side, pulling the data from the mainframe.

If you use IXF format, you can then use the IMPORT command with the
INSERT_CREATE option to create the table on UDB.

If you have lots of data suggest you do the export first with no data, and
use IMPORT to create the table. Then do the export again for all the
data, but use the LOAD (without logging) command to put the data into UDB.

You'll still need to define the indexes by hand, but you can use db2look
to generate these for you (from the mainframe). Or you can use your
favourite mainframe tool to produce the index DDL for you.

HTH

Phil Nelson

On Thu, 11 Sep 2003 19:30:13 -0600, Mark A wrote:

> Stephen Jenkinson" <sp.jenkinson@student.qut.edu.au> wrote in message
> news:4f7c9ed9.0309111641.543aa4e@posting.google.co m...

>> Hello.
>>
>> We are tring to migrate a bunch of table that resides db2 (version 6 I
>> believe) on a MVS mainframe environment to DB2 UDB version 7.0 on a
>> windows NT environment.
>>
>> We are tring to achive this by exporting and importing in the IXF
>> format, however the PC/IXF format appears to differ from that export
>> from the mainframe.
>>
>> Does anyone have any suggestion on how to migrate these tables
>> accross. There are around 100 tables, some of which have alot of
>> colunms.
>>
>> Thanks in advance

>
> You can create some sequential MVS files with the DB2 sample program
> DSNTIAUL (See Appendix D of the Version 6 Utilities Manual) and then
> download the files to NT. Make sure you convert packed (comp-3) and binary
> (comp) fields to display numeric (maybe with the CAST function on the select
> that extract the data). You will need to download the files as MVS text, not
> binary, using a program like IBM's Personal Communications that will convert
> the data from EBCDIC to ASCII.
>
> Then you can use import/load utility on DB2 for Windows. Obviously, you will
> have to create the tables and indexes, etc on DB2 for Windows before-hand.
>
> Just out of curiosity, what did you use to create the IXF extract on the
> mainframe?
>
> I realize you may not have much control over this, but it runs a lot better
> under Windows 2000.

[DB2]

Stephen,

Actually any ODBC driver should do the trick, DB2 Connect, StarSQL,
HiT, you name it!

Bob

Philip Nelson <newsaccess@scotdb.com> wrote in message news:<pan.2003.09.12.18.44.48.608721@scotdb.com>.. .

> Stephen,
>
> If you have DB2 Connect access to mainframe DB2 then you can run the
> export command from the UDB side, pulling the data from the mainframe.
>
> If you use IXF format, you can then use the IMPORT command with the
> INSERT_CREATE option to create the table on UDB.
>
> If you have lots of data suggest you do the export first with no data, and
> use IMPORT to create the table. Then do the export again for all the
> data, but use the LOAD (without logging) command to put the data into UDB.
>
> You'll still need to define the indexes by hand, but you can use db2look
> to generate these for you (from the mainframe). Or you can use your
> favourite mainframe tool to produce the index DDL for you.
>
> HTH
>
> Phil Nelson
>
> On Thu, 11 Sep 2003 19:30:13 -0600, Mark A wrote:
>

> > Stephen Jenkinson" <sp.jenkinson@student.qut.edu.au> wrote in message
> > news:4f7c9ed9.0309111641.543aa4e@posting.google.co m...

> >> Hello.
> >>
> >> We are tring to migrate a bunch of table that resides db2 (version 6 I
> >> believe) on a MVS mainframe environment to DB2 UDB version 7.0 on a
> >> windows NT environment.
> >>
> >> We are tring to achive this by exporting and importing in the IXF
> >> format, however the PC/IXF format appears to differ from that export
> >> from the mainframe.
> >>
> >> Does anyone have any suggestion on how to migrate these tables
> >> accross. There are around 100 tables, some of which have alot of
> >> colunms.
> >>
> >> Thanks in advance

> >
> > You can create some sequential MVS files with the DB2 sample program
> > DSNTIAUL (See Appendix D of the Version 6 Utilities Manual) and then
> > download the files to NT. Make sure you convert packed (comp-3) and binary
> > (comp) fields to display numeric (maybe with the CAST function on the select
> > that extract the data). You will need to download the files as MVS text, not
> > binary, using a program like IBM's Personal Communications that will convert
> > the data from EBCDIC to ASCII.
> >
> > Then you can use import/load utility on DB2 for Windows. Obviously, you will
> > have to create the tables and indexes, etc on DB2 for Windows before-hand.
> >
> > Just out of curiosity, what did you use to create the IXF extract on the
> > mainframe?
> >
> > I realize you may not have much control over this, but it runs a lot better
> > under Windows 2000.

[PM \(pm3iinc-nospam\)]

I'd have to scan the doc again but i think IXF actually stores the index
definition.

PM

[P. Saint-Jacques]

IXF will include the index definitions if:
1) the select is of type select * from table
2) The index is user built as opposed to system built ( as in not from:
create table (empid int not tull primary key(empid)....)
Regards, Pierre.

PM (pm3iinc-nospam) wrote:

> I'd have to scan the doc again but i think IXF actually stores the index
> definition.
>
> PM
>
>

[P. Saint-Jacques]

IXF will include the index definitions if:
1) the select is of type select * from table
2) The index is user built as opposed to system built ( as in not from:
create table (empid int not tull primary key(empid)....)
Regards, Pierre.

PM (pm3iinc-nospam) wrote:

> I'd have to scan the doc again but i think IXF actually stores the index
> definition.
>
> PM
>
>