473,242 Members | 1,471 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

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 8017
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: James Hong | last post by:
Help please, I try to sending an email from my html page using the java applet. but it give error on most of the PC only very few work, what is the error i make the java applet show as below ...
2
by: Dave Brueck | last post by:
Below is some information I collected from a *small* project in which I wrote a Python version of a Java application. I share this info only as a data point (rather than trying to say this data...
4
by: Jeff | last post by:
Hi, Are there anyone can help me? My questions are as following: Because I have a tools bar which coding with javascript, it will reuse very often. So I want to separate the client-side java...
14
by: technocrat | last post by:
db2 -t -v -f/home.../filename >output_file-name I have a java stored procedure..which has to run the above command...not sure how i can run this command through java.. any suggestions are...
3
by: Subrat Das | last post by:
Hi, I have a java application which calls a stored procedure to insert data into a table.Multiple threads of java call the same procedure at the same time. Sometimes it happens that few threads send...
7
helpwithcode
by: helpwithcode | last post by:
Hi people, I am just learning java.I have been creating a project which involves JDBC Connectivity.I find that the statements, String string_dob=text_dob.getText(); //Converting string to...
3
by: klh | last post by:
We've just recently converted to DB2 V8 z/os for our backend database server (upgraded from DB2 V7). Our java applications run under WebSphere/Windows using the DB2 Type 4 driver (V8 FP14). ...
2
by: vijaykumardahiya | last post by:
Hello Sir, I have a simple Issue but It is not resolve by me i.e input parameter are not store in Ms-Access. I store the input parameter through Standard Action <jsp:useBean>. jsp:useBean call a...
2
by: yeshello54 | last post by:
so here is my problem...in a contact manager i am trying to complete i have ran into an error..we have lots of code because we have some from class which we can use...anyways i keep getting an error...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.