Temporary tables

teldb2kals

Temporary tables
Hi,

I wd like some understanding of the scope of (created) temporary
tables.

Can I a stored procedure call a subprogram (say P1) which populates a
temporary table (say A), and then the SP calls another subprogram (P2)
which reads from the same temp. table ? Is this scenario workable ?

In my test case, I see that when subprogram P1 does the insert, select
count(*) after the insert returns 0.

But when I do the insert in the SP directly, the count(*) after the
insert returns '1'. And then when I try to read the same table thru
subprogram P2, the count(*) returns 0.

Note : the process is similar to an existing batch job. The only
difference is I am trying to replace the batch driver module with a
stored procedure.

Am still reading a bit abt temp. tables, but just thought wd get the
list's opinions as well.

Thanx in advance.

Cheers,
Kals


----------------
Powered by telstra.com



Phil Grainger

Re: Temporary tables
(in response to teldb2kals)
The point to bear in mind at all times with (Created) Global Temporary
Tables is that ALL data placed in them by ONE unit of work will be visible
for the duration of that unit of work ONLY

So...

1. INSERT 1 row
2. SELECT COUNT(*) returns "1"

1. INSERT 1 row
2. COMMIT
3. SELECT COUNT(*) returns "0"

It matters not whether any or all of these steps are occurring in main
programs, subprograms, stored procedures, user defined functions, triggers
etc etc etc, all that matters is whether they are all in the SAME UNIT OF
WORK or not.

For example, pseudo-conversational CICS transactions have implicit commits
so you cannot pass data between them in a GTT.......

Hopefully this clarifies things a little

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: teldb2kals [mailto:[login to unmask email]
Sent: 09 January 2002 02:44
To: [login to unmask email]
Subject: [DB2-L] Temporary tables


Hi,

I wd like some understanding of the scope of (created) temporary
tables.

Can I a stored procedure call a subprogram (say P1) which populates a
temporary table (say A), and then the SP calls another subprogram (P2)
which reads from the same temp. table ? Is this scenario workable ?

In my test case, I see that when subprogram P1 does the insert, select
count(*) after the insert returns 0.

But when I do the insert in the SP directly, the count(*) after the
insert returns '1'. And then when I try to read the same table thru
subprogram P2, the count(*) returns 0.

Note : the process is similar to an existing batch job. The only
difference is I am trying to replace the batch driver module with a
stored procedure.

Am still reading a bit abt temp. tables, but just thought wd get the
list's opinions as well.

Thanx in advance.

Cheers,
Kals


----------------
Powered by telstra.com





teldb2kals

Re: Temporary tables
(in response to Phil Grainger)
Thanx, Phil. It clarifies and confirms what I thought.

I later solved my problem. After a few tests, I realised that actually
no SQL was being executed correctly in the subprogram. I then found
that the problem had arisen due to the way I had set up DSNHLI as an
alias of DSNALI in the SPAS Steplib. (covered in a different mail).

I have now rectified it, and the programs r working as designed. But
funnily, when I was getting the problem, the SQLcode was displayed as 0
after the SQL though the SQL was not able to execute at all. The
subprogram got called, and then, ............... no idea what happens
to the SQL calls. inside the subprogram. Omegamon Trace did not show
any subprogram SQL activity at all.

For now, I have stopped worrying abt it as the problem has been fixed,
though might get back to it later.

Cheers,
Kals


-----Original Message-----
From: Grainger, Phil [SMTP:[login to unmask email]
Sent: Wednesday, January 09, 2002 9:06 PM
To: [login to unmask email]
Subject: Re: Temporary tables

The point to bear in mind at all times with (Created) Global Temporary
Tables is that ALL data placed in them by ONE unit of work will be
visible for the duration of that unit of work ONLY

So...

1. INSERT 1 row
2. SELECT COUNT(*) returns "1"

1. INSERT 1 row
2. COMMIT
3. SELECT COUNT(*) returns "0"

It matters not whether any or all of these steps are occurring in main
programs, subprograms, stored procedures, user defined functions,
triggers etc etc etc, all that matters is whether they are all in the
SAME UNIT OF WORK or not.

For example, pseudo-conversational CICS transactions have implicit
commits so you cannot pass data between them in a GTT.......

Hopefully this clarifies things a little

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: teldb2kals [ <mailto:[login to unmask email]>]
Sent: 09 January 2002 02:44
To: [login to unmask email]
Subject: [DB2-L] Temporary tables


Hi,

I wd like some understanding of the scope of (created) temporary
tables.

Can I a stored procedure call a subprogram  (say P1) which populates a
temporary table (say A), and then the SP calls another subprogram (P2)
which reads from the same temp. table ? Is this scenario workable ?

In my test case, I see that when subprogram P1 does the insert, select
count(*) after the insert returns 0.

But when I do the insert in the SP directly, the count(*) after the
insert returns '1'. And then when I try to read the same table thru
subprogram P2, the count(*) returns 0.

Note : the process is similar to an existing batch job. The only
difference is I am trying to replace the batch driver module with a
stored procedure.

Am still reading a bit abt temp. tables, but just thought wd get the
list's opinions as well.

Thanx in advance.

Cheers,
Kals


----------------
Powered by telstra.com



Gopalakrishna (CTS) Umapathy

Re: Temporary tables
(in response to James Campbell)


When you execute a COMMIT statement, DB2 deletes the isntance of temporary
table , exception to this is,
when the cursor for accessing the created table is defined WITH HOLD and is
open.

Cheers,
Gopal.

> -----Original Message-----
> From: Grainger, Phil [SMTP:[login to unmask email]
> Sent: Wednesday, January 09, 2002 3:36 PM
> To: [login to unmask email]
> Subject: Re: Temporary tables
>
> The point to bear in mind at all times with (Created) Global Temporary
> Tables is that ALL data placed in them by ONE unit of work will be visible
> for the duration of that unit of work ONLY
>
> So...
>
> 1. INSERT 1 row
> 2. SELECT COUNT(*) returns "1"
>
> 1. INSERT 1 row
> 2. COMMIT
> 3. SELECT COUNT(*) returns "0"
>
> It matters not whether any or all of these steps are occurring in main
> programs, subprograms, stored procedures, user defined functions, triggers
> etc etc etc, all that matters is whether they are all in the SAME UNIT OF
> WORK or not.
>
> For example, pseudo-conversational CICS transactions have implicit commits
> so you cannot pass data between them in a GTT.......
>
> Hopefully this clarifies things a little
>
> Phil Grainger
> Computer Associates
> Product Manager, DB2
> Tel: +44 (0)161 928 9334
> Fax: +44 (0)161 941 3775
> Mobile: +44 (0)7970 125 752
> [login to unmask email]
>
>
> -----Original Message-----
> From: teldb2kals [ <mailto:[login to unmask email]>]
> Sent: 09 January 2002 02:44
> To: [login to unmask email]
> Subject: [DB2-L] Temporary tables
>
>
> Hi,
>
> I wd like some understanding of the scope of (created) temporary
> tables.
>
> Can I a stored procedure call a subprogram (say P1) which populates a
> temporary table (say A), and then the SP calls another subprogram (P2)
> which reads from the same temp. table ? Is this scenario workable ?
>
> In my test case, I see that when subprogram P1 does the insert, select
> count(*) after the insert returns 0.
>
> But when I do the insert in the SP directly, the count(*) after the
> insert returns '1'. And then when I try to read the same table thru
> subprogram P2, the count(*) returns 0.
>
> Note : the process is similar to an existing batch job. The only
> difference is I am trying to replace the batch driver module with a
> stored procedure.
>
> Am still reading a bit abt temp. tables, but just thought wd get the
> list's opinions as well.
>
> Thanx in advance.
>
> Cheers,
> Kals
>
>
> ----------------
> Powered by telstra.com
>
>
>
> the DB2-L webpage at < http://www.ryci.com/db2-l > . The owners of the list
> can
>

James Campbell

Re: Temporary tables
(in response to teldb2kals)
Not quite true - if you have an open CURSOR ... WITH HOLD on
the CTT, the rows are kept across a commit. (At least that what the
manual says, I've never done it.)

James Campbell


On 9 Jan 2002 at 10:05, Grainger, Phil wrote:

> The point to bear in mind at all times with (Created) Global Temporary
> Tables is that ALL data placed in them by ONE unit of work will be visible
> for the duration of that unit of work ONLY
>
> So...
>
> 1. INSERT 1 row
> 2. SELECT COUNT(*) returns "1"
>
> 1. INSERT 1 row
> 2. COMMIT
> 3. SELECT COUNT(*) returns "0"
>
> It matters not whether any or all of these steps are occurring in main
> programs, subprograms, stored procedures, user defined functions, triggers
> etc etc etc, all that matters is whether they are all in the SAME UNIT OF
> WORK or not.
>
> For example, pseudo-conversational CICS transactions have implicit commits
> so you cannot pass data between them in a GTT.......
>
> Hopefully this clarifies things a little
>
> Phil Grainger
> Computer Associates
> Product Manager, DB2
> Tel: +44 (0)161 928 9334
> Fax: +44 (0)161 941 3775
> Mobile: +44 (0)7970 125 752
> [login to unmask email]
>