Alternate Approach?

On Aug 20, 2:32 pm, "pankaj_wolfhun...@yahoo.co.in"
wrote:
> Greetings,
> We need to export out data from db2 tables to flat files with '||'
> at the
> end of each record (as record delimiter).
>
> For now, we are thinking of hardcoding the value for record
> delimiter.
>
> Something like:
>
> SELECT COL1, COL2||'||'
> FROM TABLENAME
>
> Problem comes when the last column is a null value. In that case the
> export just hangs.
> Also we have to make sure that the last column is compatible with the
> record delimiter.
> (For integer, we have to explicitly do a CHAR(COLUMN)
>
> For now, we thinking of replacing the null values with some specific
> pattern which can then later be removed.
>
> But still we are not sure about our whole approach.
>
> I mean what approach we should be following in order to have a record
> delimiter with each record?
>
> Any pointers in this regard will be helpful.
>
> TIA

Hi, Pankaj.

As far as appending a double-pipe at the end of the record, give this
a try:

SELECT COALESCE(RTRIM(CHAR(COL2)),'')||'||' FROM TABLENAME

For example:

WITH
TABLENAME (COL0, COL1, COL2)
AS
(
VALUES
('A', 1,CAST(NULL AS BIGINT)),
('B', 2,55)
)
SELECT
COL0,
COL1,
COALESCE(RTRIM(CHAR(COL2)),'')||'||' NEW_COL2
FROM
TABLENAME

COL0 COL1 NEW_COL2
---- ----------- ----------------------
A 1 ||
B 2 55||


--Jeff

pankaj_wolfhunter@yahoo.co.in wrote:
> Greetings,
> We need to export out data from db2 tables to flat files with '||'
> at the
> end of each record (as record delimiter).
>
> For now, we are thinking of hardcoding the value for record
> delimiter.
>
> Something like:
>
> SELECT COL1, COL2||'||'
> FROM TABLENAME
>
> Problem comes when the last column is a null value. In that case the
> export just hangs.
> Also we have to make sure that the last column is compatible with the
> record delimiter.
> (For integer, we have to explicitly do a CHAR(COLUMN)
>
> For now, we thinking of replacing the null values with some specific
> pattern which can then later be removed.
>
> But still we are not sure about our whole approach.
>
> I mean what approach we should be following in order to have a record
> delimiter with each record?
>
> Any pointers in this regard will be helpful.
>
> TIA
>

One way is to export the file as usual and then replace EOL with ||.
Example:

[db2inst1@wb-01 ~]$ db2 "export to myfile.csv of del select COURSE_ID,
EDUCATIONORG_ID, CREDIT from nya.course fetch first 5 rows only"
SQL3104N The Export utility is beginning to export data to file
"myfile.csv".

SQL3105N The Export utility has finished exporting "5" rows.

[db2inst1@wb-01 ~]$ sed -e "s/$/||/" < myfile.csv
"ANA002","BTH",+005.0||
"DVA002","BTH",+005.0||
"DVB002","BTH",+005.0||
"DVC005","BTH",+005.0||
"ETA025","BTH",+005.0||


/Lennart