SQL stmt to find if table is in load pending state?

technocrat

How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)

Apr 12 '06 #1

Shashi Mannepalli

Check this in SYSTEM MONITOR GUIDE

tablespace_state_change_object_id - State Change Object Identification
monitor element
Cheers...
Shashi Mannepalli
technocrat wrote:

How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)

Apr 12 '06 #2

Pierre Saint-Jacques

Do a Google search on: db2 and snapshot table fubnctions
You get to:
http://publib.boulder.ibm.com/infoce...n/t0010418.htm
The URL may have wrapped in this answer.

Look at the SNAPSHOT_TBS function. it returns, amongst other things, the
state of the tblsp.
Given it's a table function, you can use it as SQL.
HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"technocrat" <su************@gmail.com> a écrit dans le message de news:
11**********************@j33g2000cwa.googlegroups. com...

How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)

Apr 13 '06 #3

Pierre Saint-Jacques

OOOPS!
My apologies but the snapshot_tbs table function will not return the table
space state.
Mes excuses, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"technocrat" <su************@gmail.com> a écrit dans le message de news:
11**********************@j33g2000cwa.googlegroups. com...

How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)

Apr 13 '06 #4

technocrat

i am actually trying to use the sysproc.db2load as a callable statement
and it returns some 6 OUT parameters...one of them is sqlerrorcode....
how can i actually access the value of that paramenter...like ..the
value of the 4th OUT parameter of the callable statemet...
any help appreciated..!

Apr 14 '06 #5

technocrat

ok i figured out the above one but now my doubt is once the load failed
i want to run the load with terminate command. so i will be calling
CREATE PROCEDURE SYSPROC.DB2LOAD
so in the above callable statement what would be the secnd parameter (
the select cusor ) because my load st will be "load from /dev/null of
del terminate into control.ACCOUNT nonrecoverable" so there is no
select statement, how would i do this???/?

Apr 14 '06 #6

mike_li

Correct me if i am wrong.

Just using:
select tabname, status from syscat.tables where tabname=' xxx',
from status, you can tell what state the table is.
Mike

technocrat wrote:

ok i figured out the above one but now my doubt is once the load failed
i want to run the load with terminate command. so i will be calling
CREATE PROCEDURE SYSPROC.DB2LOAD
so in the above callable statement what would be the secnd parameter (
the select cusor ) because my load st will be "load from /dev/null of
del terminate into control.ACCOUNT nonrecoverable" so there is no
select statement, how would i do this???/?

Apr 14 '06 #7

Bob [IBM]

mike_li wrote:

Correct me if i am wrong.

Just using:
select tabname, status from syscat.tables where tabname=' xxx',
from status, you can tell what state the table is.
Mike

technocrat wrote:

ok i figured out the above one but now my doubt is once the load failed
i want to run the load with terminate command. so i will be calling
CREATE PROCEDURE SYSPROC.DB2LOAD
so in the above callable statement what would be the secnd parameter (
the select cusor ) because my load st will be "load from /dev/null of
del terminate into control.ACCOUNT nonrecoverable" so there is no
select statement, how would i do this???/?

Unfortunately no ... the status field indicates the check pending status
and is not related to the LOAD status:
The check pending status of the object:

* N = Normal table, view, alias or nickname
* C = Check pending on table or nickname
* X = Inoperative view or nickname
Bob

Apr 14 '06 #8

Bob [IBM]

Pierre Saint-Jacques wrote:

OOOPS!
My apologies but the snapshot_tbs table function will not return the
table space state.
Mes excuses, Pierre.

Pierre - It seems the snapshot_tbs_cfg table function is what you want here?

Apr 14 '06 #9

technocrat

So finally whats the solution????? snapshot _tbs_cfg? I have no idea
how that should be implemented..any suggestons..I will google also...

Apr 14 '06 #10

Pierre Saint-Jacques

As Bob [IBM] pointsout further, I used the wrong function:
This is the one you want: snapshot_tbs_cfg
It provides column:
TABLESPACE_STATE
The following link explains the retrun code values:
http://publib.boulder.ibm.com/infoce...n/t0010418.htm

HTH, Pierre.
PS - See what Google can do for you? With help from people to set me right,
I can find stuff!
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Pierre Saint-Jacques" <se*****@invalid.net> a écrit dans le message de
news: gE**********************@wagner.videotron.net...

OOOPS!
My apologies but the snapshot_tbs table function will not return the table
space state.
Mes excuses, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"technocrat" <su************@gmail.com> a écrit dans le message de news:
11**********************@j33g2000cwa.googlegroups. com...

How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)

Apr 15 '06 #11