473,883 Members | 1,521 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.

Feb 1 '07 #1
11 8666
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.

Feb 2 '07 #2
Timofmars wrote:
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.
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.

Feb 2 '07 #3
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.
On Feb 2, 3:47 pm, Juan Singh <j...@anonymous .comwrote:
Timofmars wrote:
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.

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.

Feb 5 '07 #4

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?

While stranded on information super highway Timofmars wrote:
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.
On Feb 2, 3:47 pm, Juan Singh <j...@anonymous .comwrote:
>Timofmars wrote:
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.

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.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@ 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.
Feb 6 '07 #5
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.
On Feb 6, 10:38 am, Hemant Shah <s...@typhoon.x net.comwrote:
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?

While stranded on information super highway Timofmars wrote:
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.
On Feb 2, 3:47 pm, Juan Singh <j...@anonymous .comwrote:
Timofmars wrote:
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.
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.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMails...@ 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.

Feb 6 '07 #6
Timofmars wrote:
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
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
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.
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?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 6 '07 #7
On Feb 6, 2:03 pm, Knut Stolze <sto...@de.ibm. comwrote:
Timofmars wrote:
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

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
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.

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?

--
Knut Stolze
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.

Feb 6 '07 #8

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.

While stranded on information super highway Timofmars wrote:
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.
On Feb 6, 10:38 am, Hemant Shah <s...@typhoon.x net.comwrote:
>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?

While stranded on information super highway Timofmars wrote:
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.
On Feb 2, 3:47 pm, Juan Singh <j...@anonymous .comwrote:
Timofmars wrote:
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.
>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.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMails...@ 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.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@ 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.
Feb 6 '07 #9
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<sh**@typho on.xnet.com02/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.

Feb 7 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1731
by: MLH | last post by:
Can I read the name of the current database's record locking file from within code? I would expect to read something like "MyDB.ldb"
1
2418
by: Jay A. Moritz | last post by:
I have an ASP .NET 2003 application that uses an Access 2003 database. Trimming my application down to just one page that connects to the database and uses an OleDbDataReader to get info, then closes the Reader and then the Connection, I still get the .LDB Record-Locking file staying open on the server. Does ASP .NET not close it's connection even after the code closes the connection?? The server is Windows 2003 (Application does work...
3
1542
by: Tom McLaughlin | last post by:
I am new to vb.net and I am running into problems understanding DataBinding and its concept. The only examples I find are always talk about Web design, I only want to use this data on my standalone computer. So my question is: I have a sequential file that contains data and the data is structured. is there a way to load this data into a DataGrid control? If the answer is yes, then how do I accomplish this?
3
1734
by: kd | last post by:
Hi All, I have a .txt file, whose contents need to be read and processed. Which among the following is the best way to do it? - Using Sequential file access functions (FileOpen, LineInput, etc) - Using binary file access functions (File.Open, FileGet, etc) Thanks, kd
5
4305
by: leesa | last post by:
I have a number field in a sequential file. Im going to read this file, sum up this number and display the grand total in my application using VB. Do anyone know how to do that?? Thanks in advance!
6
1186
by: Cashewz | last post by:
Using a menu item to write to a sequential file from the parent form, what code and where would I put it to write the text values, settings, bool values from various child menues?
19
3419
by: jason2412 | last post by:
Hi, Not sure if this is the right place to post this query so apologies in advance. I have two columns in an access table. Example for 2 records: Field 1 - Retail Field 2 - Sat
1
2470
by: dhingchangs | last post by:
Hi, I want to read a sequential file character by character and the check each character for the position and process further. The lenght of file can be anything. Actually my purpose is read a file which contains the format of the other file and chop the other file in the format of first to give third file. Can anybody help me as I am new to VB? Thanks in advance Prasad
1
2931
by: stevedub | last post by:
I am having some trouble configuring my array to read from a sequential file, and then calling on that to fill an array of interests. I think I have the class set up to read the file, but when I run my program the rates array does not get the information. I think my problem is where I am actuall calling the array index, but I am not sure how to do this. Here is my code: /* * MortFrame.java * * Created on February 24, 2008, 7:28 PM */...
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11109
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10728
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10405
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9558
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5980
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4602
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.