I'd like to put a constraint, a check or a foreign key, on two combined fields from table1 to another field in table2. Here is what I tried, but both gave me errors: ALTER TABLE table1 ADD CONSTRAINT foo CHECK (field1 || field2 IN (SELECT fieldx FROM table2)); ALTER TABLE table1 ADD CONSTRAINT foo FOREIGN KEY (field1 || field2)...
I have a table of non trivial size on a DB2 database that is updated X times a day per user input in another application. This table is also read by my web-app to display some info to another set of users. I have a large number of users on my web app and they need to do lots of fuzzy string lookups with data that is up-to-the-minute accu...
If I run the following query: select load_cyc_num , crnt_dnlq_age_cde , sum(cc_min_pymt_amt) as min_pymt , sum(ec_tot_bal) as budget , case when ec_tot_bal > 0 then 'Y' else 'N' end as budget , case when ac_stat_cde in ('A0P','A1P','ARP','A3P') then 'Y' else 'N' end as arngmnt , sum(sn_close_bal) as st_bal from statements where...
Hi, i was getting this error com.ibm.db2.jcc.b.SqlException: Invalid argument: unknown column name COL1 when trying to access my resultSet with rs.getString("COL1"). My SQL Query is: Select UPPER(COL1) from table1. the same query and Java code is working fine with DB2 v8(Type 2 driver) but it is throwing the above exception when using w...
Folks, Is it mandatory to use an ALIAS when we are doing some operation on the column? Ex: select upper(col1) from table1 when i am trying to retrieve the resultset by rs.getString("col1"), it was giving this exception COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0611E Invalid column name. SQLSTATE=S0022 when I changed the qu...
Is it possible to do an INSERT statement inside a scalar C-language UDF in DB2? I know it sounds weird to have an UDF with side effects like this, but there's a good reason for it. ...
I started learning .net about 3 years ago. I have gone thru a boot camp during that time learning OO and various data access technologies such as NHibernate, Subsonic, LINQ TO SQL. didn't wanna try EF cause it hasn't reached version 3 :) As far as reporting goes, I have heard that many ORM'S fall flat on their face when it comes to rep...
Is this possible to call c/c++ application from DB2 stored procedure/trigger? If yes, what would be general steps doing it? Any pointers to web resources on the subject? Thanks! ...
Hi, I want to insert an item to a table in the database. The table has a key that is auto generated. Is it possible to override the auto generated key, (force a value). If so how? ...
For DB2... table1.a is varchar(30) and has an index on it. table2.b is varchar(45) and has an index on it. Will table1.a = table2.b use the index on table1, table2, or both? Although it would seem obvious that it should use both indexes, I'm asking because I believe on sybase, this would only use one of the indexes (I'm not sure which...
I have successfully connected to DB2 database using db2jcc.jar and now facing another issue. When i create a dataset and preview it. BIRT viewer throws following error. Cannot get the decimal value from column: 1. Cannot get BigDecimal value in the result set. SQL error #1: [IBM][JDBC Driver] CLI0610E Invalid column number. SQLSTATE=S1...
I have a problem with DB2 databases that should store unicode characters. The connection is established using JDBC. What do I have to do if I would like to insert a unicode string into the database? INSERT INTO my_table(id, string_field) VALUES(1, N'my unicode string'); or INSERT INTO my_table(id, string_field) VALUES(1, 'my unicod...
I'm attempting to create percentile scores. My query generates the ranks correctly, but the divide does nothing(the ranks are displayed in the columns rather than scores) "/"(RANK() OVER(ORDER BY "Disk IO"),Count(*)) "Disk IO Score" I've also tried generating the rank then selecting that and dividing, but it has the same result. SELE...
From my application written with Delphi 2009, I am reading from and writing to an IBM DB2 database using ADO, the ODBC Provider for ADO, and ODBC: Application -> ADO (Win32) -> ODBC-Provider for ADO -> ODBC -> (net) -> DB2 (Windows) Now, my application need to store unicode data. So I set my database codepage to UTF-8. Delphi handles s...
when I develop application with DB2 I try to debug but can not. What can I do to debug procedure, function, views, and trigger on DB2. ...
Say I have a database table T with 4 fields, A, B, C, and D. A, B, and C are the primary key. For any combination of [A, B], there is always a row where C == spaces. There may or may not be other rows where C != spaces. I have a query that gets all rows where [A, B] == [in_a, in_b], and also where C == in_c if such a row exists, or C...
I have created a table like this: CREATE TABLE A ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 INCREMENT BY +1 NO MINVALUE NO MAXVALUE NO CYCLE CACHE 20 NO ORDER ) , ID_MIRROR CHAR(20)) I would like to do an insert such that ID would be automatically set, and ID_MIRROR would be what is in ID, but prefixed with ...
Hi, I am trying to extract a column value from the "first" row that satisfies some criteria, where "first" is defined by a hard-coded list of column values. Unfortunately I'm a SQL amateur. I'm using DB2. I can do it inefficiently like this: SELECT COALESCE( (SELECT COL FROM SOMETABLE WHERE X = "txt1" AND Y = "txt2" AND COL = 'A'), ...
In a UNIX-y way, I'm trying to start a process, background it, and tie the lifetime of that process to my shell. What I'm talking about isn't simply backgrounding the process, I want the process to be sent SIGTERM, or for it to have an open file descriptor that is closed, or something when the shell exits, so that the user of the shell ...
I've created a user-maintained MQT (Materialized Query Table) in DB2 9.7 (not yet fixpack 1). It's a simple grouping of the main fact table. But my queries aren't being rewritten to hit it. Here's what I've tried: Created the MQT with ENABLE QUERY OPTIMIZATION and MAINTAINED BY USER characteristics Also included REFRESH DEFERRED & D...