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
Tried anything yourself? What failed / went wrong?
What do you mean by "fixed length file"? Even though a program could use a x-count byte raster to read & interpret data in a file, I don't think this is a usual style / file type in *nix. You mayhap mean a text file with a fixed line length? This you can achive in SQL itself using some formatting commands, I'm sure.
Create a Jun_07142014.sql file, with the following code.
EXPORT TO SL_JUN_07142014.asc of del modified by coldel| messages SL_JUN_07142014.expmsg
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;
Then, on the command line to generate the Jun_07142014.out file.
Jun_07142014.sql > Jun_07142014.out
Then open the Jun_07142014.out file and remove the double quotes and pipe (|) delimiters.
I want to be able to do all that in a script so that, when i run that script, it automatically generates the output file.
Can you extract the data you need running this from a shell script?
How long are the lines and how long should they be?
Why do you get pipes and quotes in the output? Is it in the source data?
Does your query language support commands such these
[list]
SET LINESIZE 4000
COLUMN x FORMAT A20
[/list]
I'm still a little unclear as to what output you want. RudiC did ask, but you didn't answer. Do you want a fixed line length file, fixed width columns or are you planning to FTP this to a mainframe where you can define data as fixed record?
If you could help us to understand your need, then perhaps we will be better placed to help you with a solution.
Firstly, i have a SQL script.
I'm doing a "EXPORT TO SL_JUN_07142014.asc of del modified by coldel|" before the SQL script so that, the columns are separated by a "|".
Then since, i'm directing this output to a .out file, it is wrapping each field with double quotes.
The SQL script itself has the lengths needed for each field. I want to retain those lengths; that's all.
Does your query language support commands such these
SET LINESIZE
COLUMN x FORMAT A20
I'm using DB2 UDB. I'm not sure, i follow your question.
These are Oracle SQL statements. I'm not sure if they follow some greater standard, but the first tries to define the output line as 4000 characters wide (probably a bit excessive) and the second is to force each column to a specified width. If you set each column to a fixed size, then the output should be fixed width fields for each record.
Sorry if I've confused things.
Can you post a sample of what you can generate already from your script and tell us what is needs to look like? Feel free to sanitise it, but make sure that the columns still match the required length. We should be able to work with that and ignore the 'how it gets extracted' bit.