How to use the SET TERMINATOR directive to change the delimiter to newline?

[kirbybowl]

I have been using --#SET TERMINATOR to change the delimiter within my CLP scripts. In my situation, the CLP scripts are generated from a database of CLP commands (written by many authors), thus the delimiter varies. My script will add the --#SET TERMINATOR directive when necessary to allow multiple delimiters to exist in a single CLP script. However, there are cases where I'd like to change the delimiter to the newline character. Is there a way to do this in DB2 via --#SET TERMINATOR (or any other methods)?

Thanks,

Steve

[docdiesel]

Hi,

I'm not sure if it works, but in LOAD commands I use the hex codes of the delimiters with the parameters COLDEL and CHARDEL. It looks like this:

LOAD from myfile.txt OF DEL CHARDEL 0x22 COLDEL 0x3B  INTO ...

I guess that SET TERMINATOR accepts these as well. Here are some of the most common codes:

" 0x22    ; 0x3B    \n 0x0A (newline)
' 0x27    , 0x2C    \  0x20 (space)

Please let us know if this works.

Regards,

Bernd

[kirbybowl]

Hi,

I'm not sure if it works, but in LOAD commands I use the hex codes of the delimiters with the parameters COLDEL and CHARDEL. It looks like this:

LOAD from myfile.txt OF DEL CHARDEL 0x22 COLDEL 0x3B  INTO ...

I guess that SET TERMINATOR accepts these as well. Here are some of the most common codes:

" 0x22    ; 0x3B    \n 0x0A (newline)
' 0x27    , 0x2C    \  0x20 (space)

Please let us know if this works.

Regards,

Bernd

Hi Bernd,

Unfortunately, that won't work. You will get the following error message:

DB21109E  The syntax is incorrect for the "--#SET TERMINATOR" directive.  Reason Code: "1"

From my experience with SET TERMINATOR, it can accept a one or two character delimiter. It won't accept Hex because the delimiter will be too long, and thus give the above error message.

For example, these are perfectly valid SET TERMINATOR statements:

--#SET TERMINATOR @
--#SET TERMINATOR @#

and these are not valid statements:

--#SET TERMINATOR
--#SET TERMINATOR 0x0A

I've been looking into this for some time and I need a solution, so I've just decided to modify the way I generate my CLP code so it won't ever need to deal with the newline delimiter.

Thanks for your suggestion

Steve