By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,585 Members | 2,235 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,585 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
10 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.