Export DB2 Data To Fixed Length Text File

[jtrapat1]

I need to extract some data from db2 to a fixed length file so that it can be read by some Microsoft Access macros.
I don't think I'll have to join two or more tables - it may be coming all from one table.
(I'll post more info later, if this is the case.)

I need to be able to set the starting and ending positions of these fields so that the macro from access can move the data into its matching fields in the access tables.

Is there a way to do this from db2?

I was thinking of using the export utility to create a text file but I don't know too much about it.
The main thing is that I need to specify the column positions.


Thanks in Advance.
John

 

[blom0344]

Hello John,

Apart from technicalities, you are much better of with using ODBC to link the DB2 tables in the Access application. I have a whole bunch of applications that work this way, and it does away with all the **** (dirty word) of exporting and importing of text files. You can very easily define update queries, use macro's to trigger them and use a switchboard manager to give user-friendly manipulation. All you need is ODBC components from the DB2 client (perhaps using generic Microsoft drivers will do the trick too) to get things working. If you need technical details, mail me.......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[ummadhira]

T.Blom is right for extracting small tables but if volume is larger then Access won't handle it. It's my experience, so for large volumes I use a JCL

//*
//UNLOAD2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSPUNCH DD DISP=NEW,CATLG,DELETE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=800),
// SPACE=(TRK,(2,2)),UNIT=SYSDA,
// DSN=TSOHRBC.C000713.LOADCARD.VCDW.SUBD.TEST1031
//SYSREC00 DD UNIT=SYSDA,SPACE=(CYL,(5,5)),
// DSN=TSOHRBC.C000713.UNLOADED.VCDW.SUBD.TEST1031,
// DISP=(NEW,CATLG,DELETE)
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBWT)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
LIB('DBWT.RUNLIB.LOAD') -
PARM('SQL')
//SYSIN DD *
SELECT *
FROM
DB2ADM.table_name;
//

Modify the JCL as you needed

Madhira

 

[blom0344]

Hello Madhira,

Ouch, were do you run this.........
For large amounts of data, the standard ODBC time-out setting should possibly set to a higher interval than the standard 60 seconds.........
But than again, Access works best with limited amount of data anyway........ T. Blom
Information analyst
tbl@shimano-eu.com

 

[gregsimpson]

John,

try something like the following

db2 export to c:\temp\employeeoutput of DEL messages
C:\temp\errors.txt select FNAME, SNAME from EMPLOYEE


I believe this will give you files of fixed length. I think it uses the length of the fields in the DDL to establish the format for the output files.

Therefore each record on the file should always look the same and you'll be able to reference it accordingly.

Greg

 

[jnelms]

The EXPORT utility is flexible enough to take SQL statements like select cast(intvalue as char(12)),cast(charval as char(12)) from mytable. In this way you can force aliegnments and left justify all fields.

 

[jtrapat1]

jnelms,

Thanks for the response.
I was just about to ask that question about alignment.

I can handle all the character data if I use substr(dob_field,2,3) but I have a problem with fields defined as sqlint32 and sqlint64.

By your code above, are you saying that I can cast these integer types and write them out as strings of a fixed length?
So, for your above example, the intvalue would be 12 characters long?

Reply when you can.

Thanks
John

 

[jtrapat1]

jnelms,

I answered my own question - thanks for the help.
Do you know if there is any way I can strip out the single quotes that get inserted around each data value?
-----------------------------------------------
Here's the statement I used:

db2 "EXPORT TO test1.txt OF DEL MODIFIED BY CHARDEL'' SELECT cast(adds_id as char(5)), substr(comment1,1,80),substr(comment2_priv,1,80) FROM nysa.bp12t_approp_adds"
------------------------------------------------

And, here's one record output to the file:
'271 ','PART OF $27.5M REQUEST; FIRST PRIORITY ','

I know it's wrapping here but it's coming out the way I'd like. I would just have to change the starting positions in access to account for the single quotes.

Thanks again.
John

 

[dsearfass]

John,

We create fixed length files using the DB2 export in conjunction with the substr() function and the contact operator. I believe the syntax for the concat is a double bar ||.

Your select would look like:

db2 "EXPORT TO test1.txt OF DEL MODIFIED BY CHARDEL'' SELECT cast(adds_id as char(5)) || substr(comment1,1,80) || substr(comment2_priv,1,80) FROM nysa.bp12t_approp_adds"

What this will do is remove all the quotes and commas between fields in the exported data. If you need the quotes to separate each field, then cast them in your select i.e cast("," as char(1))|| where ever you want a comma to show up.

 

[jnelms]

Hi john,

I'm thinking that there might be other ways to do the same thing. Specifically, you don't like the quotes. You can look into using just a select statement like "Select cast(...)||..." and you would not have to use EXPORT.

You can turn off the column headers by using (from a command prompt) "db2 -x select cast(...)||..." and this will give you column data without quotes. And you can redirect the output of the query to a file "> myoutput.txt".

An alternative is to look in the DB2 Docs "Command Reference" Chapter 2. Specifically the UPDATE COMMAND OPTIONS. Use the X on and R on outputfile.txt. And also the DB2CMD.

Here's an example to script this out from a dos prompt:
db2cmd /c /w /i db2 -tffile.sql
where the file.sql has set the output file and turned off column headings for the sql statements.

There is more than one way to do this so you have options to choose from.

Jim

 

[troyu]

Hi - I am trying to export to a fixed-length file, and I am almost there. I get the file in fixed-length format, however I have double quotes on the far left and far right of the file now. The output looks like this:

"312 My Store 12112"
"991 Your Store 11200"

My statement is as follows:
export to C:\data.txt of DEL MODIFIED BY CHARDEL"" select cast(storenum as char(6)) || cast(desc as char(55)) || cast(zipcode as char(55)) from TableA;

I tried with a single double quote, but I receive an error message when attempting this.

 

[troyu]

I would also like to include a new line character at the end of each line within the file produced. Is there a way to include this within my SELECT statement, or is there another way?

 

[gregsimpson]

troyu,

I think

thread178-657487 both your posts.

Cheers
Greg