Fixed Length File from DB2 SQL

Anyone?

you don't need a script
all you need is to run the db2 command have it output to a file (i think you can set some flags to strip all the headers)

if that does not work try to use the export command (i think that is the name of the command, haven't used it in a while...)

Momi, i have been using the export command with pipe delimiter first. Then i'm redirecting the result to an out file. Then i open the file and substitute all the double quotes around each field with blank.
Then i replace all the pipe characters with blank.

I want yo be able to create a script that does all that.

The Q editor can do this

21:47:17$ cat t5
"had quotes"
a | pipe
21:47:22$ q -o -q -i "y \" ' '^Jy | ' '^Js^Jq^J" t5
21:48:23$ cat t5
 had quotes 
a   pipe
21:48:57$ 

Select allOpen in new window

You can get the Q editor from https://github.com/duncan-roe/q/releases/tag/v19
Fetch the tar.gz
tar xf q-19.tar.gz
cd q-19/q
make
cp q /usr/local/bin (or ~/bin if you have one)

I cannot install new software at my client location,
Is it not doable by a script at all?

Sorry, posted wrong answer.
BTW: Why did your script not work?

Ok I think I understood now.
If the name of your output file will be "sl_jun2014_out.txt", try this:

$ db2 -rsl_jun2014_out.txt  <<EOF 
CONNECT ...
SELECT CAST( COALESCE( Cl_Id, '000000000' ) AS CHAR( 9 )) AS Cl_Id
     , Status
     , CAST( COALESCE( Placement_Category, '' ) AS CHAR( 3 )) AS Placement_Category
     , CAST( COALESCE( Dob_Dt, '' ) AS CHAR( 8 )) AS Dob_Dt
     , CAST( COALESCE( Ldss, '' ) AS CHAR( 3 )) AS Ldss
     , CAST( COALESCE( First_Nm, '' ) AS CHAR( 20 )) AS First_Nm
     , CAST( COALESCE( Last_Nm, '' ) AS CHAR( 20 )) AS Last_Nm
     , CAST( COALESCE( Middle_Initial, '' ) AS CHAR( 1 )) AS Middle_Initial
     , CAST( COALESCE( Street_Number, '' ) AS CHAR( 5 )) AS Street_Number
     , CAST( COALESCE( Street_Nm, '' ) AS CHAR( 20 )) AS Street_Nm
     , CAST( COALESCE( Additional_Street_, '' ) AS CHAR( 20 )) AS Additional_Street_Nm
     , CAST( COALESCE( City_Nm, '' ) AS CHAR( 15 )) AS City_Nm
     , CAST( COALESCE( State_Cd, '' ) AS CHAR( 2 )) AS State_Cd
     , CAST( COALESCE( Pa_Adr_Zip5_No, '' ) AS CHAR( 5 )) AS Pa_Adr_Zip5_No
     , CAST( COALESCE( Ssn_No, '000000000' ) AS CHAR( 9 )) AS Ssn_No
     , CAST( COALESCE( School_Name, '' ) AS CHAR( 10 )) AS School_Name
  FROM Sl_Jun2014;
EOF

Select allOpen in new window

Now, if you already have a text file with the script and named it (for example) "sl_jun2014.cmd", then you can do this:

db2 -tvf sl_jun2014.cmd -r sl_jun2014.txt

Select allOpen in new window

I have created the following script and named it as test_script.sql.

db2 -rsl_jun2014_out.txt  <<EOF 
CONNECT TO OHEP
SELECT CAST( COALESCE( Cl_Id, '000000000' ) AS CHAR( 9 )) AS Cl_Id
     , Status
     , CAST( COALESCE( Placement_Category, '' ) AS CHAR( 3 )) AS Placement_Category
     , CAST( COALESCE( Dob_Dt, '' ) AS CHAR( 8 )) AS Dob_Dt
     , CAST( COALESCE( Ldss, '' ) AS CHAR( 3 )) AS Ldss
     , CAST( COALESCE( First_Nm, '' ) AS CHAR( 20 )) AS First_Nm
     , CAST( COALESCE( Last_Nm, '' ) AS CHAR( 20 )) AS Last_Nm
     , CAST( COALESCE( Middle_Initial, '' ) AS CHAR( 1 )) AS Middle_Initial
     , CAST( COALESCE( Street_Number, '' ) AS CHAR( 5 )) AS Street_Number
     , CAST( COALESCE( Street_Nm, '' ) AS CHAR( 20 )) AS Street_Nm
     , CAST( COALESCE( Additional_Street_, '' ) AS CHAR( 20 )) AS Additional_Street_Nm
     , CAST( COALESCE( City_Nm, '' ) AS CHAR( 15 )) AS City_Nm
     , CAST( COALESCE( State_Cd, '' ) AS CHAR( 2 )) AS State_Cd
     , CAST( COALESCE( Pa_Adr_Zip5_No, '' ) AS CHAR( 5 )) AS Pa_Adr_Zip5_No
     , CAST( COALESCE( Ssn_No, '000000000' ) AS CHAR( 9 )) AS Ssn_No
     , CAST( COALESCE( School_Name, '' ) AS CHAR( 10 )) AS School_Name
  FROM Sl_Jun2014;
EOF

Select allOpen in new window

Now, i'm running this command

db2 -tvf test_script.sql

Select allOpen in new window

I'm getting these errors.

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1024N  A database connection does not exist.  SQLSTATE=08003

Select allOpen in new window

SQL1024N  A database connection does not exist.  SQLSTATE=08003

How do you connect to your database?
Did you source (include) the DB2 profile of the instance you are trying to use?
You may have to provide the correct CONNECT statement.

is this command correct?

db2 -rsl_jun2014_out.txt  <<EOF

Select allOpen in new window

Yes, it signifies db2 will execute the following commands "inline" (which you did not code) up to the "EOF".

I gave you two methods to execute your script, use the second one.
But first remove the "db2" command from your test script (line 1), and execute as follows:

db2 -tvf test_script.sql  -routput_file.txt

Select allOpen in new window

Mike, your last post executes fine.
But it is loosing the fixed length between the fields.
We need to maintain the fixed length as we generate the file.

Mmmm.....Been a long time since I used db2.

See if this one works, otherwise we may need to try the "EXPORT" command:

db2 -tvf test_script.sql   > output_file.txt

Select allOpen in new window

OK found it...

1) Change your sql script as follows:

CONNECT TO OHEP
SELECT CAST( COALESCE( Cl_Id, '000000000' ) AS CHAR( 9 )) AS Cl_Id
    || Status
    || CAST( COALESCE( Placement_Category, '' ) AS CHAR( 3 )) AS Placement_Category
    || CAST( COALESCE( Dob_Dt, '' ) AS CHAR( 8 )) AS Dob_Dt
    || CAST( COALESCE( Ldss, '' ) AS CHAR( 3 )) AS Ldss
    || CAST( COALESCE( First_Nm, '' ) AS CHAR( 20 )) AS First_Nm
    || CAST( COALESCE( Last_Nm, '' ) AS CHAR( 20 )) AS Last_Nm
    || CAST( COALESCE( Middle_Initial, '' ) AS CHAR( 1 )) AS Middle_Initial
    || CAST( COALESCE( Street_Number, '' ) AS CHAR( 5 )) AS Street_Number
    || CAST( COALESCE( Street_Nm, '' ) AS CHAR( 20 )) AS Street_Nm
    || CAST( COALESCE( Additional_Street_, '' ) AS CHAR( 20 )) AS Additional_Street_Nm
    || CAST( COALESCE( City_Nm, '' ) AS CHAR( 15 )) AS City_Nm
    || CAST( COALESCE( State_Cd, '' ) AS CHAR( 2 )) AS State_Cd
    || CAST( COALESCE( Pa_Adr_Zip5_No, '' ) AS CHAR( 5 )) AS Pa_Adr_Zip5_No
    || CAST( COALESCE( Ssn_No, '000000000' ) AS CHAR( 9 )) AS Ssn_No
    || CAST( COALESCE( School_Name, '' ) AS CHAR( 10 )) AS School_Name
  FROM Sl_Jun2014;

Select allOpen in new window

2) Execute as follows:

db2 -x +o -tf test_script.sql +o -routput_file.txt

Select allOpen in new window

Mike, didn't work..wasn't creating any file with that command.
 We are on AIX.

I'm sorry for the time lapse..
 Mike, yes i have made changes to your code, but
as soon as i dump into a .txt file, the format is changed to tab delimited; not fixed length.

Can you post (or attach) your script and the exact command you are using?

My apologies..i have been travelling.
 Once i redirected the file to *.asc, it worked out for me.
Thanks for all your time and effort!