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

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

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:
>

>
You can run a simple INSERT statement through JDBC without any problem.
>

>
Have a look at the ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY WITH EMPTY
TABLE statement.
>
--
Knut Stolze
DB2 Information Integration Development
IBM Germany

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

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:
>

>
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

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:

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

what is the performance difference between
- "ALTER TABLE ... NOT LOGGED INITIALLY EMPTY TABLE"
AND
- "DELETE FROM ... " ?




Serge Rielau schrieb:

Stefan wrote:

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