PL/1 DB2 V8 Table Declaration for a CLOB

Hi,

I am writing a PL/1 program which will be reading a DB2 Table which
has a column defined as a CLOB.
Therefore, I should use DCLGEN to obtain accurate SQL DECLARE TABLE
statements.
However I do not have authority, at this company, to use DCLGEN and am
trying to learn more about CLOBs, so trying to bypass any formal
processes.
Is there anyone who could supply the relevant SQL DECLARE TABLE and
relevant PL/1 host variables for the following table:
CREATE TABLE LSTEK54.PP_MRA_SMT(
SEQ_NUMBER INTEGER GENERATED ALWAYS AS IDENTITY,
TYPE CHAR(2) NOT NULL,
LOTS_OF_DATA CLOB(4M),
PRIMARY KEY (SEQ_NUMBER)
)IN LSTEK54.PPMS999T
AUDIT NONE
;

If there is anything more I need to supply please just ask.

Much appreciated
Moira

msmithuk wrote:
> Hi,
>
> I am writing a PL/1 program which will be reading a DB2 Table which
> has a column defined as a CLOB.
> Therefore, I should use DCLGEN to obtain accurate SQL DECLARE TABLE
> statements.
> However I do not have authority, at this company, to use DCLGEN

Explain to someone higher up that this policy was established by a
drunken lunatic.

> and am
> trying to learn more about CLOBs, so trying to bypass any formal
> processes.
> Is there anyone who could supply the relevant SQL DECLARE TABLE and
> relevant PL/1 host variables for the following table:
> CREATE TABLE LSTEK54.PP_MRA_SMT(
> SEQ_NUMBER INTEGER GENERATED ALWAYS AS IDENTITY,
> TYPE CHAR(2) NOT NULL,
> LOTS_OF_DATA CLOB(4M),
> PRIMARY KEY (SEQ_NUMBER)
> )IN LSTEK54.PPMS999T
> AUDIT NONE
> ;
>
> If there is anything more I need to supply please just ask.

The DECLARE TABLE would simply be

DECLARE LSTEK54.PP_MRA_SP TABLE (
SEQ_NUMBER INTEGER,
TYPE CHAR(2) NOT NULL,
LOTS_OF_DATA CLOB(4M));




Integer is "FIXED BINARY(31,0)" and CHAR(2) is CHARACTER(2), but a CLOB
should be declared by the magic DB2 statement:

DECLARE LOTS_OF_DATA SQL TYPE IS CLOB(4M);

which will be translated into

DECLARE 1 LOTS_OF_DATA,
3 LOTS_OF_DATA_LENGTH FIXED BIN(31),
3 LOTS_OF_DATA_DATA,
4 LOTS_OF_DATA_DATA1(128) CHAR(32767),
4 LOTS_OF_DATA_DATA2 CHAR(128);

--
John W. Kennedy
"The bright critics assembled in this volume will doubtless show, in
their sophisticated and ingenious new ways, that, just as /Pooh/ is
suffused with humanism, our humanism itself, at this late date, has
become full of /Pooh./"
-- Frederick Crews. "Postmodern Pooh", Preface

On 23 Jul, 19:29, John W Kennedy <jwke...@attglobal.net> wrote:
> msmithuk wrote:
> > Hi,
>
> > I am writing a PL/1 program which will be reading a DB2 Table which
> > has a column defined as a CLOB.
> > Therefore, I should use DCLGEN to obtain accurate SQL DECLARE TABLE
> > statements.
> > However I do not have authority, at this company, to use DCLGEN
>
> Explain to someone higher up that this policy was established by a
> drunken lunatic.
>
> > and am
> > trying to learn more about CLOBs, so trying to bypass any formal
> > processes.
> > Is there anyone who could supply the relevant SQL DECLARE TABLE and
> > relevant PL/1 host variables for the following table:
> > CREATE TABLE LSTEK54.PP_MRA_SMT(
> > * * SEQ_NUMBER * * *INTEGER GENERATED ALWAYS AS IDENTITY,
> > * * TYPE * * * * * *CHAR(2) *NOT NULL,
> > * * LOTS_OF_DATA * *CLOB(4M),
> > * * PRIMARY KEY (SEQ_NUMBER)
> > )IN LSTEK54.PPMS999T
> > AUDIT NONE
> > ;
>
> > If there is anything more I need to supply please just ask.
>
> The DECLARE TABLE would simply be
>
> DECLARE LSTEK54.PP_MRA_SP TABLE (
> * *SEQ_NUMBER * * *INTEGER,
> * *TYPE * * * * * *CHAR(2) *NOT NULL,
> * *LOTS_OF_DATA * *CLOB(4M));
>
> Integer is "FIXED BINARY(31,0)" and CHAR(2) is CHARACTER(2), but a CLOB
> * should be declared by the magic DB2 statement:
>
> DECLARE LOTS_OF_DATA SQL TYPE IS CLOB(4M);
>
> which will be translated into
>
> DECLARE 1 LOTS_OF_DATA,
> * *3 LOTS_OF_DATA_LENGTH FIXED BIN(31),
> * *3 LOTS_OF_DATA_DATA,
> * * *4 LOTS_OF_DATA_DATA1(128) CHAR(32767),
> * * *4 LOTS_OF_DATA_DATA2 CHAR(128);
>
> --
> John W. Kennedy
> * "The bright critics assembled in this volume will doubtless show, in
> their sophisticated and ingenious new ways, that, just as /Pooh/ is
> suffused with humanism, our humanism itself, at this late date, has
> become full of /Pooh./"
> * *-- Frederick Crews. *"Postmodern Pooh", Preface

Brilliant, thanks alot.

Moira,

You can find "at your company" :-) stuff on DSNTEP2 in
SYSN.DB2.SDSNSAMP(DSNTEP2)
SYSN.DB2.SDSNSAMP(DSNTEJ1U)

Code and sample.

By the way, will it be a need to have to compare such data stored in
CLOB ? If yes, maybe I will have to adapt my software !! Holiday
work?

Cheers
Pierre

On 25 Jul, 06:38, PeD <pierre.devill...@evola.be> wrote:
> Moira,
>
> You can find "at your company" :-) stuff on DSNTEP2 in
> SYSN.DB2.SDSNSAMP(DSNTEP2)
> SYSN.DB2.SDSNSAMP(DSNTEJ1U)
>
> Code and sample.
>
> By the way, will it be a need to have to compare such data stored in
> CLOB ? If yes, maybe I will have to adapt my software !! Holiday
> work?
>
> Cheers
> Pierre

Hi, they have not yet worked out the full data model design yet of
this data yet so do not know at present. I will be back to this forum
once I get more detailed requirements. I am hoping to be the expert on
this one.