Asynchronous procedure call with DB2 .NET Data Provider
Is there any way to asychronously call a DB2 stored procedure using DB2 .NET Data Provider? ...
db2
More efficient of the two queries?
I have a table with columns user_id, email, default. Default stores 'Y' or 'N' depending if the email is the users default email. Each user can have only one default email. When a user is doing an update or insert on the table, in my SP I check if the user has passed isDefault as 'Y'. If so, I need to update all the entries for that us...
Combining a select statement of boolean
Hi guys could you please help me out here. SELECT e.name, IF e.active = 0 THEN e.active = true WHERE e.id = #{estate_id} ELSE e.active = false WHERE e.id = #{estate_id} END IF AS Estate_status FROM estates e ...
Inserting a row into DB2 from a sub-select - NULL error
I am trying to insert a row into a table, using a value that is derived from another table. Here is the SQL statement that I am trying to use: INSERT INTO NextKeyValue(KeyName, KeyValue) SELECT 'DisplayWorkItemId' AS KeyName, (MAX(work_item_display_id) + 1) AS KeyValue FROM work_item; So, I am trying to create a row in NextKeyValue t...
How can I share a database connection across a forked process in Perl?
I made the following programs in Perl before: my $db = DBconnection with DB2 if ($pid = fork()) { #parent } else { #child $db->execute("SELECT ****"); exit; } wait(); $db->execute("SELECT ****"); I thought that it waited for the end of the child process to have wanted to do it and would operate it for DB by a pro-pro...
How can I drop multiple columns in DB2 on AIX?
What is the syntax to drop multiple columns in DB2? I tried this: ALTER TABLE work_item DROP COLUMN (TRANSACTION_TYPE, REQUEST_TYPE, LOCATION_CODE, ROLE_ID, SUBMITTED_BY); But it didn't work. I am running on AIX (from what I read, it looks like maybe you cannot drop columns at all on platforms other than AIX). If I drop the columns...
db2 referential integrity problem
Situation is pretty serious, we have a table in DB2 on AS400 which has defined foreign key to another table, so we are entering record which have regular ID of referenced table so when we enter SQL insert through front end tool everything went fine. Problem arises when this insert is to be done through java application which uses Sprin...
Too many columns in a single database table?
An application I am dealing has a single table with 170 columns. In my mind, it would make more sense to break this table up into smaller, more logical groupings, though you could have an issue where numerous tables have to be joined if you needed data from columns in the various tables. I am wondering what the pros and cons of these...
unique indexes and include statements
create unique index In DB2 UDB I can create an index using the following syntax create unique index I_0004 on TABLENAME (a) INCLUDE (b, c, d); where a, b, c and d are field of the table TABLENAME. In DB2 for os390 this syntax (the INCLUDE keyword) is not allowed, so I am creating the indexes as follows create unique index...
DB2 Equivalent to SQL's GO?
I have written a DB2 query to do the following: Create a temp table Select from a monster query / insert into the temp table Select from the temp table / delete from old table Select from the temp table / insert into a different table In MSSQL, I am allowed to run the commands one after another as one long query. Failing that, I can ...
How can you enable transactions with IBM.Data.DB2.iSeries for stored procedures
I am trying to write a client which executes a series of DB2 iSeries Stored procedures in DB2. I am using the IBM.Data.DB2.iSeries provider and need all my calls to be within a .NET transaction. Seems like everything is ignored and data being committed.Any pointers... ...
SQL Dump from DB2
Hi, I'm trying to dump the contents of a particular schema in one IBM DB2 UDB server into an sql text file (much like the mysqldump functionality of mysql). I came across db2look, but it only dumps the structure of the schema (only ddl, no dml). So how can I get my thing done? jrh. ...
DB2 Temp Tables: Not storing or not retrieving information
I'm an MSSQL guy, but I'm working on a DB2 query that needs to create a temp table, insert into it, and do stuff with it. As a much-shortened test, I'm using the following query, which is providing the same result.. declare global temporary table tt_testingSyntax (id int); insert into session.tt_testingSyntax (id) values (1); insert i...
DB2 Bulk Load from .Net (C#)?
Is there an API for loading a large # of non-transactional rows directly into a DB2 table? I know there are some CLI commands for doing this, but it isn't clear what is directly supported from the .Net APIs. ...
ODBC Command does not throw error
I'm working on a VB6 executable that uses ODBC to update a DB2 Table. When trying to update a row that does not exist the program does not throw an error as would be expected. Why would this happen? objAdoConn.Execute("Update T1234 Set A = 'X' Where B = 'y'"); ...
Unit Testing data from DB2
I have a .NET project (Test project) in VS2008 and am doing some unit tests for the data in DB2. I am finding that the tests are not transactional and I have to phyiscally go and delete the data or run a function to clean the data before I run the test even though I am using ITransaction. Can any one point why? ...
XML to Relational with DB2 and Java (and Hibernate?)
What I have here is a bunch of XML-Files containing data and a nice ER-Model to which the data belongs. What my problem is: I need to get this data into a db2. The tables with all necessary attributes and keys are already created. I was thinking of three different solutions: Parsing the XML and creating SQL-Queries from it. This solut...
Cannot create DB2 index, getting SQL30081N error
Trying to create an index (and run some long queries) on DB2 v9.1 and failing with the following error message: SQL30081N (A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "". Communication function detecting the e...
Dynamic SQL vs Static SQL
Hi All, In our current codebase we are using MFC db classes to connect to DB2. This is all old code that has been passed onto us by another development team, so we know some of the history but not all. Most of the Code abstracts away the creation of SQL queries through functions such as Update() and Insert() that prepend something like...
Why does DB2 CHAR() function return '24:00:00' for a time field that is 00:00:00?
I'm querying DB2 on i (AS/400). A plain SELECT statement on a column with datatype of TIME returns 00:00:00, but when I use the CHAR() function it returns '24:00:00'. I understand that 24:00:00 is a valid time, but why would CHAR() return 24 when the native TIME returs 00? ...