Getting number of rows inserted on IBM DB2 V6R1 (AS400) through JBDC We migrated recently to newer V6R1 version of DB2 on AS400, we are using Spring framework (v. 2.5.6.) to communicate with the database. We are calling Spring's NamedParameterJdbcTemplate.update() method to insert new rows, this method should return number of inserted rows, what is not happening (we got zero back as a result) although row is being inserted regularly. We concluded that if there is no primary key column in insert statement everything is ok, so there is no problem when PK column is autoincrement, but in some situations we must insert PK value and then we must cope somehow with situation that regularly inserted row is not being registered with JDBC or Spring. Can somebody help?

removing first occurrence of a character in DB2 I have a column with values as below: BOOK # ,7,8BOOK # ,2BOOK # ,13,14I am generating above column with complex xml aggregate function as below: SELECT CONCAT('BOOK # ',XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(',', SUBSTR(TRIM(TEMP.BOOK_NUM), LOCATE('.',TEMP.BOOK_NUM)+1)))) AS VARCHAR(1024 )))FROM TEMP GROUP BY BOOK_IDTemp.book_num is of type 123.2, 123.4 in two different rows. So book_id 123 will have two books 2, 4. Thus to extract 2 and 4 out of two rows of same book_id, I had to write above query. As clear there is an extra ',' comma coming after the words 'BOOK #' which is redundant and unrequired. I am not able to modify above query to remove it. Is there any way possible to remove the first occurrence of a character (comma here) from a column value which could be accomodated in the query above ? My output should be: BOOK # 7,8BOOK # 2BOOK # 13,14Thanks for reading!

SSIS DB2 and Distributed Transactions I am using Microsoft OLEDB Provider for DB2 to connect to DB2 data base. I have a sequence container and two execute SQL tasks inside that.When I set the container transaction option to "Required", I am getting error- "Error Description: Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection"I have set MSDTC properties in server to Network DTC Access, Allow remote Clients, Allow InBound and AllowOutbound, and No authentication required, still I am getting the same error. Update:I have done the same DTC setting on my local machine too where package is running. Still the same error exists.I created sample package which implements transaction on SQL table and this works fine. Is there any additional setting I need to do for DB2 database?

Image uploading and Retrieval from DB2 H! I'm trying to upload an image in a DB2 database.The image size is a JPG (6.76 kb - 6924 bytes). The database table has a BLOB field of length 1048576. My code to insert the image is as follows: If fileup.PostedFile IsNot Nothing AndAlso fileup.PostedFile.FileName <> "" Then Dim imagesize As Byte() = New Byte(fileup.PostedFile.ContentLength - 1) {} Dim uploadedimage1 As HttpPostedFile = fileup.PostedFile uploadedimage1.InputStream.Read(imagesize, 0, CInt(fileup.PostedFile.ContentLength)) Dim uploadedimage2 As New OleDbParameter("@Image", OleDbType.VarBinary, imagesize.Length) uploadedimage2.Value = imagesize Dim cmd As New OleDbCommand() cmd.CommandText = "INSERT INTO xxx_TBL(x, IMAGE) VALUES (?, ?)" cmd.Parameters.Add(x) cmd.Parameters.Add(uploadedimage2) cmd.Connection = clsDatabase.Open_DB() Dim result As Integer = cmd.ExecuteNonQuery() If result > 0 Then Return TrueThe image gets inserted into the Database. The code to get the image from the Database, into a DataTable, which is then bound to a GridView to display on the webpage is as follows: Dim cmd As New OleDbCommand()cmd.CommandText = "SELECT x, IMAGE FROM xxx_TBL WHERE y = 1" cmd.Connection = clsDatabase.Open_DB()Dim dReader As OleDbDataReaderdReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)Dim dt As New DataTabledt.Columns.Add(New DataColumn("Comments", GetType(String)))dt.Columns.Add(New DataColumn("Picture", GetType(Bitmap)))Do While (dReader.Read()) Dim dr As DataRow = dt.NewRow() dr("Comments") = dReader(0).ToString Dim imageobj = dReader(1) Using ms As New System.IO.MemoryStream Dim bm As Bitmap Dim bytearray = DirectCast(imageobj, Byte()) ms.Write(bytearray, 0, bytearray.length) bm = New Bitmap(ms) dr("Picture") = bm End Using dt.Rows.Add(dr)LoopGridView1.DataSource = dtGridView1.DataBind()x comes from the Database fine, and is displayed. However, I get no picture - just a small "missing picture (red cross on white) icon". On checking the database, the length of the image in BLOB field is 8192. However, on copying it onto a 'TEST' File (no extension), the size was 13848 bytes. I'm guessing that could be because of the way DB2 reads/encodes the image binary, but I'm not sure.Could someone please highlight the possible causes of error? Any suggestions on making this work or debugging?

show all tables in DB2 using the LIST command This is embarrassing, but I can't seem to find a way to list the names of the tables in our DB2 database. Here is what I tried: root@VO11555:~# su - db2inst1root@VO11555:~# . ~db2inst1/sqllib/db2profileroot@VO11555:~# LIST ACTIVE DATABASESWe receive this error: SQL1092N "ROOT" does not have the authority to perform the requested command or operation. The DB2 version number follows. root@VO11555:~# db2levelDB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09071"with level identifier "08020107".Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23034", and Fix Pack"1".Product is installed at "/opt/db2V9.7".

DB2 Update with join queries I am using DB2 for performing the below update operation. update DATA set B_DESC=P_DESC, P_DESC=nullwhere B_DESC= *, P_DESC=*(Select B_DESC,P_DESC from C_DATA) The below is actually possible but since complex joins are involved in that sub query it is not advisable to use like below update DATA set B_DESC=P_DESC, P_DESC=nullwhere B_DESC= (Select B_DESC from C_DATA), P_DESC=(Select P_DESC from C_DATA)I have to update DATA table, but the B_DESC and P_DESC i have to fetch it from C_DATA table and use it in the UPDATE query. Please let me know how to do it. It has to be a single query if possible. Thanks in advance.

DB2 Express V10.1 and db2cc binary I have just installed DB2 Express v10.1 with a personnalised installation where i have checked all composants but i can't find the db2cc binary to launch the control center. someones know if db2cc binary has been renamed, deleted and how launch the control center ? Thanks.

DB2 SQL to delete row if no foreign references, or update otherwise I have a DB2 database with three tables: locations, items, movement_history. The items table has a foreign key, location_id, into locations. The movement_history table has a foreign key, item_id, into items, and two foreign keys, location_id_before and location_id_after, into locations. I want to delete a location if no row in items or movement_history references it. If the location row is referenced somewhere, I just want to update it to be marked as deleted. Is there a way to do this in one statement? What's the best way to do this?

"First Steps" shortcut is missing after successful DB2 installation I have installed a DB2 Express-c verion 10.1 on Windows-XP, after successful installation, It has not asked to create a SAMPLE database as I saw in 9.7 version. Then I go to launch it maunally as 'Start > Programs > IBM DB2 > DB2COPY1 (Default) > First Steps'. But I have not found any shortcut for this. Then I opened the db2cmd and run the command db2fs to launch it maunally but got the error: 'db2fs' is not recognized as an internal or external commandWhats wrong with my DB2 setup?? Thanks in advance.

LINQPad DB2 Support I know that I am able to connect LINQPad to an EF Model and be able to run queries to get results and lambda expressions back, but for DB2, it does not show any SQL due to the provider not being handed over to LINQPad by EF. It seems that if I want to be able to see the SQL, I need to write a custom driver for DB2 to get it to work. I have read the documentation and it is a bit over my head. Does anyone have an example of this working, I would greatly appreciate it. Thanks.