DB2 Unload (to record sequential file) on NT possible?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Timofmars - 01 Feb 2007 17:49 GMT

I'm try to Unload DB2 data from a table into a record sequential file
on NT. I can an unload on Mainframe, but it doesn't seem to be an
option in NT.

In NT, all I can do is export/import. I can do a Load too, but not an
Unload. I just want the only the data from the table in a record
sequential file. Export seems only to give options to have a delimited
line sequential file or a record sequential file where the data is
preceeded by table setup information.

So is there a way to unload data on NT? Thanks.

Reply to this Message

andyhe - 02 Feb 2007 15:51 GMT

You could try exporting that table delimited , with no character
delimiter and a very special column delimiter (export to file of del
modified by nochardel, coldel~ select blablabla...), and then find and
replace all column delimiters by 'nothing'. Although I don't see why a
delimited file couldn't work just as well as a record layout. It's the
same data, you just need to treat it differently to import back again.
With a little program/script this should be easy.

Reply to this Message

Juan Singh - 02 Feb 2007 20:47 GMT

> I'm try to Unload DB2 data from a table into a record sequential file
> on NT. I can an unload on Mainframe, but it doesn't seem to be an
[quoted text clipped - 7 lines]
>
> So is there a way to unload data on NT? Thanks.

What is your end goal? Do you want to import data in another DB2
instance running on NT? Try using WinSQL
(http://synametrics.com/winsql). This program allows moving data from
one source to another target.

Reply to this Message

Timofmars - 05 Feb 2007 17:51 GMT

No, I'm not trying to import the data into another DB2 instance.
Instead, we have a COBOL program that reads exported data and creates
a VSAM backup file from the data. Normally, it would read data
directly from the DB2 tables to create the backup.

However, I've created something very similar to what andyhe suggested
where I export the data in a delimited file and have a COBOL program
parse out the delimiters. This is about twice as fast as the method of
reading directly from the tables.

But rather than continue using delimited export data, a record
sequential export file would be ideal. The UNLOAD command works on
Mainframe, but apparently not on NT. The NT option of an IPX format
export seems to be record sequential, but there is a header file
containing column information that I don't want. I can't think of an
easy way to skip over or remove that column data either. So I was just
looking for a way to get a pure record sequential file with only data.

Thanks for the replies so far.

> > I'm try to Unload DB2 data from a table into a record sequential file
> > on NT. I can an unload on Mainframe, but it doesn't seem to be an
[quoted text clipped - 12 lines]
> (http://synametrics.com/winsql). This program allows moving data from
> one source to another target.

Reply to this Message

Hemant Shah - 06 Feb 2007 15:38 GMT

Are you running COBOL program on NT to create VSAM sequential file, and then
load it to Main Frame?

Reading data directly from the database and creating sequential file should
be faster then exporting it and reading export file.

Can you post the SQL statement you are using in the COBOL program?

> No, I'm not trying to import the data into another DB2 instance.
> Instead, we have a COBOL program that reads exported data and creates
[quoted text clipped - 32 lines]
>> (http://synametrics.com/winsql). This program allows moving data from
>> one source to another target.

Signature

Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

Reply to this Message

Timofmars - 06 Feb 2007 18:43 GMT

I'm not porting anything to a different system. The NT and Mainframe
are seperate. I'm using the mainframe unload as an example of how the
unload option does what I want it to do on mainframe. It gives me a
record sequential file that can be read directly into a copybook.

On NT, unload isn't an option. Instead, I have to use a delimited
export file (line sequential file) on NT.

On MVS, I have:

UNLD DATA FORMAT ( DSNTIAUL )
FROM TABLE DB2T.OAAAA
WHERE (OAA_FILESET_CD = '%$JFSET%')
ORDER BY CLUSTER

On NT I have:

DB2 EXPORT TO %$DB2FILE% OF DEL MODIFIED BY COLDEL~ DECPT` NOCHARDEL
MESSAGES %$DB2LOG% SELECT * FROM DB2ADMIN.AAAA WHERE (OAP_FILESET_CD =
'%$JFSET%') Order by 1, 2, 3

The COBOL programs read these files to create a custom/proprietary
backup file. This has shown to be about twice as fast a reading
directly from the DB2 tables. It may be because many cursors have to
opened for each table, I don't know. But the problem with the NT
export is that it's not as reliable as the mainframe unload, since it
requires that there's no delimiters in the actual column data, and
that our compiler has set COBSW=(-N) so that Low-Values/Null-Values
are handled correctly.

> Are you running COBOL program on NT to create VSAM sequential file, and then
> load it to Main Frame?
[quoted text clipped - 50 lines]
> I haven't lost my mind, Above opinions are mine only.
> it's backed up on tape somewhere. Others can have their own.

Reply to this Message

Knut Stolze - 06 Feb 2007 19:03 GMT

> I'm not porting anything to a different system. The NT and Mainframe
> are seperate. I'm using the mainframe unload as an example of how the
[quoted text clipped - 16 lines]
> MESSAGES %$DB2LOG% SELECT * FROM DB2ADMIN.AAAA WHERE (OAP_FILESET_CD =
> '%$JFSET%') Order by 1, 2, 3

How about:

db2 -x "SELECT ... FROM ... WHERE ..." > %DB2FILE%

That would generate something comparable to a FB data set.

> The COBOL programs read these files to create a custom/proprietary
> backup file. This has shown to be about twice as fast a reading
[quoted text clipped - 4 lines]
> that our compiler has set COBSW=(-N) so that Low-Values/Null-Values
> are handled correctly.

I don't get this. Why don't you just escape such delimiters or remove them?

Is this a problem because you are using fixed-length records on MVS and
variable-length records (with delimiters) on UDB?

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Timofmars - 06 Feb 2007 19:59 GMT

> > I'm not porting anything to a different system. The NT and Mainframe
> > are seperate. I'm using the mainframe unload as an example of how the
[quoted text clipped - 41 lines]
> DB2 z/OS Utilities Development
> IBM Germany

What's an "FB" data set?

How would I get rid of delimiters? If I don't specify the delimiters,
it takes the default delimiters like double-quotes and periods for
character string indicators and decimal point indicators.

Anyway, I have copybooks that can read in a record-sequential data
file and will immediately have the correct values in the fields. This
is what happens on MVS when reading in the unloaded db2 tables.

On NT, an unload to a record sequential file doesn't seem to be
possible because unload is not supported on NT. However, I did see the
IXF export type does create a record-sequential file. But the problem
with that is it also includes column information in a header since
this file is meant to be imported into a database, creating the tables
if they don't already exist. Your suggestion on the Select statement
did get rid of the headers for that I see. But unfortunately, that
doesn't work to eliminate the headers in the IXF export.

Reply to this Message

Knut Stolze - 07 Feb 2007 09:58 GMT

>> > I'm not porting anything to a different system. The NT and Mainframe
>> > are seperate. I'm using the mainframe unload as an example of how the
[quoted text clipped - 39 lines]
>
> What's an "FB" data set?

FB = fixed-length records and blocked. That tells z/OS (MVS) how the data
is stored on the volumes. Data sets on MVS are comparable to files.

> How would I get rid of delimiters? If I don't specify the delimiters,
> it takes the default delimiters like double-quotes and periods for
> character string indicators and decimal point indicators.

You could use a "sed" or a Perl script (or whatever you have available on
your platform) to massage the output of EXPORT in any way you like.

> Anyway, I have copybooks that can read in a record-sequential data
> file and will immediately have the correct values in the fields. This
> is what happens on MVS when reading in the unloaded db2 tables.

You haven't answered the question regarding fixed/variable length records
yet...

> On NT, an unload to a record sequential file doesn't seem to be
> possible because unload is not supported on NT.

Well, it is. It's just named EXPORT there.

> However, I did see the
> IXF export type does create a record-sequential file.

All files generated by EXPORT are "record-sequential". The difference is
just the internal encoding in the files. So what exactly do you need
there? Plain text in ASCII? (IXF is binary not text)

> But the problem
> with that is it also includes column information in a header since
> this file is meant to be imported into a database, creating the tables
> if they don't already exist. Your suggestion on the Select statement
> did get rid of the headers for that I see. But unfortunately, that
> doesn't work to eliminate the headers in the IXF export.

What I posted above only redirects the output of a query into a file.
The -x removes everything that is not data. Thus, it has nothing to do
with EXPORT or UNLOAD.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Hemant Shah - 06 Feb 2007 21:01 GMT

O.K. Now I understand what you are trying to do. But, I still do not
understand why reading directly from the database is slower than reading the
export file. Basically they both should be using same SQL statement:

SELECT COL1, COL2, ....
FROM TABLENAME
WHERE WHERE_CLAUSE
ORDER BY ORDER_BY_CLAUSE
FOR FETCH ONLY

Do you perform RUNSTATS/REBIND before you run the COBOL program?

We use COBOL, C, Perl programs to read directly from the database and
my experience is that it is faster then export/read, and require less disk
space.

> I'm not porting anything to a different system. The NT and Mainframe
> are seperate. I'm using the mainframe unload as an example of how the
[quoted text clipped - 80 lines]
>> I haven't lost my mind, Above opinions are mine only.
>> it's backed up on tape somewhere. Others can have their own.

Signature

Reply to this Message

Frank Swarbrick - 07 Feb 2007 01:27 GMT

I wonder if he's not using FOR FETCH ONLY in the COBOL program.
I mention this only because I am doing some 'performance' testing and ran in
to exactly this.

Just a thought....

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

>>> Hemant Shah<shah@typhoon.xnet.com> 02/06/07 2:01 PM >>>

O.K. Now I understand what you are trying to do. But, I still do not
understand why reading directly from the database is slower than reading
the
export file. Basically they both should be using same SQL statement:

SELECT COL1, COL2, ....
FROM TABLENAME
WHERE WHERE_CLAUSE
ORDER BY ORDER_BY_CLAUSE
FOR FETCH ONLY

Do you perform RUNSTATS/REBIND before you run the COBOL program?

We use COBOL, C, Perl programs to read directly from the database and
my experience is that it is faster then export/read, and require less disk
space.

Reply to this Message

Knut Stolze - 07 Feb 2007 10:00 GMT

> O.K. Now I understand what you are trying to do. But, I still do not
> understand why reading directly from the database is slower than reading
[quoted text clipped - 5 lines]
> ORDER BY ORDER_BY_CLAUSE
> FOR FETCH ONLY

I agree here. Exporting and then reading from the exported file will always
be slower compared to directly reading from the DB. Question is if the
application is properly coded to do that.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message