470,638 Members | 1,581 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,638 developers. It's quick & easy.

DB2 Java INSERT-Statement / truncate

Hi all,
two questions:
- is there a way to call the INSERT-Statement from Java (JDBC).
(import from file of del replace into table)

- in db2 there is no truncate-statement, is it?
Is in db2 something like that?

Thank you for all answers
Stefan

Nov 14 '06 #1
8 7756
Stefan wrote:
Hi all,
two questions:
- is there a way to call the INSERT-Statement from Java (JDBC).
(import from file of del replace into table)
You can run a simple INSERT statement through JDBC without any problem.
- in db2 there is no truncate-statement, is it?
Is in db2 something like that?
Have a look at the ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY WITH EMPTY
TABLE statement.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 14 '06 #2
Hi Knut,
sorry I thought about doing an IMPORT.
So can I use the IMPORT-Statement in JDBC?

Thank you...
Stefan

Knut Stolze schrieb:
Stefan wrote:
Hi all,
two questions:
- is there a way to call the INSERT-Statement from Java (JDBC).
(import from file of del replace into table)

You can run a simple INSERT statement through JDBC without any problem.
- in db2 there is no truncate-statement, is it?
Is in db2 something like that?

Have a look at the ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY WITH EMPTY
TABLE statement.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 15 '06 #3
Stefan wrote:
Hi Knut,
sorry I thought about doing an IMPORT.
So can I use the IMPORT-Statement in JDBC?
No, not directly. IMPORT is _not_ a SQL statement - it is a DB2 command.
You will have to use the respective API. For that, you can wrap the API
call into a stored procedure and then invoke the SP through JDBC.

I'm not sure if the ADMIN_CMD procedure supports IMPORT. If it doesn't or
if you do not yet have the ADMIN_CMD procedure, this article may help you
along: http://tinyurl.com/j5mxs

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 15 '06 #4
One more question about the "truncate":

I used the "ALTER TABLE... "-Statement as you mentioned.
After a rollback I can't access the table anymore!?!?

Knut Stolze schrieb:
Stefan wrote:
Hi Knut,
sorry I thought about doing an IMPORT.
So can I use the IMPORT-Statement in JDBC?

No, not directly. IMPORT is _not_ a SQL statement - it is a DB2 command.
You will have to use the respective API. For that, you can wrap the API
call into a stored procedure and then invoke the SP through JDBC.

I'm not sure if the ADMIN_CMD procedure supports IMPORT. If it doesn't or
if you do not yet have the ADMIN_CMD procedure, this article may help you
along: http://tinyurl.com/j5mxs

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 15 '06 #5
please see the following error:
com.ibm.db.DataException: A database manager error occurred. :
[IBM][CLI Driver][DB2/NT] SQL1477N Table "SID.TEST" cannot be
accessed. SQLSTATE=55019
Stefan schrieb:
One more question about the "truncate":

I used the "ALTER TABLE... "-Statement as you mentioned.
After a rollback I can't access the table anymore!?!?

Knut Stolze schrieb:
Stefan wrote:
Hi Knut,
sorry I thought about doing an IMPORT.
So can I use the IMPORT-Statement in JDBC?
No, not directly. IMPORT is _not_ a SQL statement - it is a DB2 command.
You will have to use the respective API. For that, you can wrap the API
call into a stored procedure and then invoke the SP through JDBC.

I'm not sure if the ADMIN_CMD procedure supports IMPORT. If it doesn't or
if you do not yet have the ADMIN_CMD procedure, this article may help you
along: http://tinyurl.com/j5mxs

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 15 '06 #6
Stefan wrote:
please see the following error:
com.ibm.db.DataException: A database manager error occurred. :
[IBM][CLI Driver][DB2/NT] SQL1477N Table "SID.TEST" cannot be
accessed. SQLSTATE=55019
You will need to drop the table. switching of logging has its price...

SQL1477N For table "<table-name>" an object "<object-id>" in
table space "<tbspace-id>" cannot be accessed.

Explanation:

An attempt was made to access a table where one of its objects is
not accessible. The table may not be accessible because of one
of the following reasons:

o The table had NOT LOGGED INITIALLY activated when the unit of
work was rolled back.

o The table is a partitioned declared temporary table and one
or more partitions failed since the temporary table was
declared (all declared temporary tables have the schema name
SESSION).

o ROLLFORWARD encountered the activation of NOT LOGGED
INITIALLY on this table or a NONRECOVERABLE load on this
table.

Access to this table is not allowed because its integrity cannot
be guaranteed.

User Response:

One of the following actions can be taken.

o If the object is a table and it had NOT LOGGED INITIALLY
activated, drop the table. If this table is required,
re-create it.

o If the object is a data partition, detach it from the table.
If this data partition is required, add a new one.

o If the object is a non-partitioned index, drop the index. If
this index is required, create a new one.

o If the table is a declared temporary table, drop the table.
If this table is required, declare it again.

o Otherwise, restore from a tablespace or database backup. The
backup image must have been taken subsequent to the commit
point following the completion of the non-recoverable
operation (NOT LOGGED INITIALLY operation, or NONRECOVERABLE
load).

The catalogs can be used to determine what the object is. To
determine if the object is a table, use the following query:
SELECT TABNAME
FROM SYSCAT.TABLES
WHERE TBSPACEID="<tbspace-id>" AND TABLEID="<object-id>"

If a table name does not appear as the result for the above
query you can determine if the object is a partition by using the
following query:
SELECT DATAPARTITIONNAME, TABNAME
FROM SYSCAT.DATAPARTITIONS
WHERE TBSPACEID="<tbspace-id>" AND
PARTITIONOBJECTID="<object-id>"

To determine if the object is an index, use the following
query:
SELECT INDNAME
FROM SYSCAT.INDEXES
WHERE TBSPACEID="<tbspace-id>" AND
INDEX_OBJECTID="<object-id>"

sqlcode : -1477

sqlstate : 55019
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 15 '06 #7
what is the performance difference between
- "ALTER TABLE ... NOT LOGGED INITIALLY EMPTY TABLE"
AND
- "DELETE FROM ... " ?


Serge Rielau schrieb:
Stefan wrote:
please see the following error:
com.ibm.db.DataException: A database manager error occurred. :
[IBM][CLI Driver][DB2/NT] SQL1477N Table "SID.TEST" cannot be
accessed. SQLSTATE=55019
You will need to drop the table. switching of logging has its price...

SQL1477N For table "<table-name>" an object "<object-id>" in
table space "<tbspace-id>" cannot be accessed.

Explanation:

An attempt was made to access a table where one of its objects is
not accessible. The table may not be accessible because of one
of the following reasons:

o The table had NOT LOGGED INITIALLY activated when the unit of
work was rolled back.

o The table is a partitioned declared temporary table and one
or more partitions failed since the temporary table was
declared (all declared temporary tables have the schema name
SESSION).

o ROLLFORWARD encountered the activation of NOT LOGGED
INITIALLY on this table or a NONRECOVERABLE load on this
table.

Access to this table is not allowed because its integrity cannot
be guaranteed.

User Response:

One of the following actions can be taken.

o If the object is a table and it had NOT LOGGED INITIALLY
activated, drop the table. If this table is required,
re-create it.

o If the object is a data partition, detach it from the table.
If this data partition is required, add a new one.

o If the object is a non-partitioned index, drop the index. If
this index is required, create a new one.

o If the table is a declared temporary table, drop the table.
If this table is required, declare it again.

o Otherwise, restore from a tablespace or database backup. The
backup image must have been taken subsequent to the commit
point following the completion of the non-recoverable
operation (NOT LOGGED INITIALLY operation, or NONRECOVERABLE
load).

The catalogs can be used to determine what the object is. To
determine if the object is a table, use the following query:
SELECT TABNAME
FROM SYSCAT.TABLES
WHERE TBSPACEID="<tbspace-id>" AND TABLEID="<object-id>"

If a table name does not appear as the result for the above
query you can determine if the object is a partition by using the
following query:
SELECT DATAPARTITIONNAME, TABNAME
FROM SYSCAT.DATAPARTITIONS
WHERE TBSPACEID="<tbspace-id>" AND
PARTITIONOBJECTID="<object-id>"

To determine if the object is an index, use the following
query:
SELECT INDNAME
FROM SYSCAT.INDEXES
WHERE TBSPACEID="<tbspace-id>" AND
INDEX_OBJECTID="<object-id>"

sqlcode : -1477

sqlstate : 55019
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 15 '06 #8
Ian
Stefan wrote:
what is the performance difference between
- "ALTER TABLE ... NOT LOGGED INITIALLY EMPTY TABLE"
AND
- "DELETE FROM ... " ?
The first simply truncates the table, and is very quick. The
issue is that if there is a problem and a ROLLBACK occurs, the
table is unusable (must be dropped/recreated).

DELETE FROM is a logged operation. As such, it can take a long
time if you have a lot of data in your database.
Nov 16 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by James Hong | last post: by
2 posts views Thread by Dave Brueck | last post: by
14 posts views Thread by technocrat | last post: by
3 posts views Thread by klh | last post: by
2 posts views Thread by yeshello54 | last post: by
1 post views Thread by Korara | last post: by
???
1 post views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.