DB2 Java INSERT-Statement / truncate

[Stefan]

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

[Knut Stolze]

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

[Stefan]

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

[Knut Stolze]

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

[Stefan]

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

[Stefan]

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

[Serge Rielau]

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

[Stefan]

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

[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.