Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Fixed Length File from DB2 SQL

Posted on 2014-07-15
19
Medium Priority
?
374 Views
Last Modified: 2014-08-06
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

0
Comment
Question by:pvsbandi
19 Comments
 

Author Comment

by:pvsbandi
ID: 40197835
Anyone?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 40198495
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
 

Author Comment

by:pvsbandi
ID: 40198979
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Expert Comment

by:Duncan Roe
ID: 40199072
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
 

Author Comment

by:pvsbandi
ID: 40199123
I cannot install new software at my client location,
Is it not doable by a script at all?
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40199262
Sorry, posted wrong answer.
BTW: Why did your script not work?
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40199351
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
 

Author Comment

by:pvsbandi
ID: 40200008
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40200174
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
 

Author Comment

by:pvsbandi
ID: 40200362
is this command correct?
db2 -rsl_jun2014_out.txt  <<EOF

Open in new window

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40200404
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
 

Author Comment

by:pvsbandi
ID: 40200484
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40203323
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40205352
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
 

Author Comment

by:pvsbandi
ID: 40205401
Mike, didn't work..wasn't creating any file with that command.
 We are on AIX.
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 2000 total points
ID: 40205607
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
 

Author Comment

by:pvsbandi
ID: 40219206
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40219398
Can you post (or attach) your script and the exact command you are using?
0
 

Author Closing Comment

by:pvsbandi
ID: 40243596
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Fine Tune your automatic Updates for Ubuntu / Debian
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Suggested Courses
Course of the Month12 days, 14 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question