Fixed Length File from DB2 SQL

Hi,

 I have a DB2 UDB 9.7 SQL script, as follows:
I need to pass the script into Unix and generate a fixed length file from this.

 Can someone kindly provide a script to achieve it?
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

Open in new window

pvsbandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pvsbandiAuthor Commented:
Anyone?
0
momi_sabagCommented:
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...)
0
pvsbandiAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Duncan RoeSoftware DeveloperCommented:
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$ 

Open 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)
0
pvsbandiAuthor Commented:
I cannot install new software at my client location,
Is it not doable by a script at all?
0
MikeOM_DBACommented:
Sorry, posted wrong answer.
BTW: Why did your script not work?
0
MikeOM_DBACommented:
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

Open 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

Open in new window

0
pvsbandiAuthor Commented:
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

Open in new window


Now, i'm running this command
db2 -tvf test_script.sql

Open 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

Open in new window

0
MikeOM_DBACommented:
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.
0
pvsbandiAuthor Commented:
is this command correct?
db2 -rsl_jun2014_out.txt  <<EOF

Open in new window

0
MikeOM_DBACommented:
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

Open in new window

0
pvsbandiAuthor Commented:
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.
0
MikeOM_DBACommented:
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

Open in new window

0
MikeOM_DBACommented:
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;

Open in new window

2) Execute as follows:
db2 -x +o -tf test_script.sql +o -routput_file.txt

Open in new window

0
pvsbandiAuthor Commented:
Mike, didn't work..wasn't creating any file with that command.
 We are on AIX.
0
MikeOM_DBACommented:
Grrrr, try this:
db2 -xtf test_script.sql  > output_file.txt

Open in new window


PS: Did you modify the script as I posted?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pvsbandiAuthor Commented:
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.
0
MikeOM_DBACommented:
Can you post (or attach) your script and the exact command you are using?
0
pvsbandiAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.