I successfully compiled some UDFs on 64-bit Linux that were originally compiled on AIX Linux is running IBM DB2 v9.5 Here's the function that sometimes returns for some values #include <cstdlib> #include <cstdio> #include <iostream> #include "cbudf.hpp" using namespace std; extern "C" void SQL_API_FN cbzdt(SQLUDF_VARCHAR_FBD *pArg,...
I'm trying to create a table with a field that has a starting date, I want to put in a check to mkae sure that a date before today cannot be entered in. This is the code i have so far for that table CREATE TABLE client_service ( NHS_num Varchar(10) NOT NULL, service_id Integer NOT NULL, starting_date Date NOT NULL CHECK(star...
We are having a debate in our company. I use left outer joins quit frequently. I was told by another programmer that there is too much over head and I should not use them. Example, Lets say I have two tables, they would rather me hit the database twice get the information I need from each table store it in memory and join the data on ...
I have a situation in BIRT reporting. A report I created is insisting on displaying some fields as blank rather than zero. The situation arises when the field is actually a sub-select that returns no rows. So for example, if the select includes: 0 as p3, then the 0 is displayed okay. If however, the select has: (select sum(other_fie...
I have an update statement that goes thus: update tableA set val1='X', val2='Y' where id in ( select id from tableA A LEFT JOIN tableB B ON A.col1=B.col1 and A.col2=B.col2 where A.col3='xx' and B.col3= 'YY') Now, the inner SELECT statement runs in 10 minutes returning 1000 records (both tableA and tableB have ab...
I am attempting to run a parameterized query against a DB2 database from .NET using the Client Access ODBC Driver using the following code: var db2Cmd = new OdbcCommand("INSERT INTO presnlats (LAT) VALUES (@LAT)", db2Conn); db2Cmd.Parameters.AddWithValue("@LAT", insertValue); Console.Out.WriteLine(db2Cmd.ExecuteNonQuery()); When execu...
I'm attempting to connect a ColdFusion application to a DB2 ODBC DSN. Here's my error message: Connection verification failed for data source: <DSN NAME> java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket][IBM][CLI Driver] SQL30082N Attempt to establish connection failed with security reason "24" ("USERNAME AND/OR P...
Just getting started with db2. Quick question about giving it SQL commands from a file. If I run: db2 -f mySQLcommands and all my commands are one line sql commands they execute with no problems. if I put newlines in the middle of my sql statements then db2 comes back with "expected such and such a character after" Here is an examp...
Database: DB2 v9.5 on AIX Scenario: I have 2 instances- db2inst1 and db2inst2. I created db2inst1 first. I went ahead and created db2inst2 next and I do 'db2set -all' on db2inst2 instance I see this: [g] DB2INSTDEF=db2inst1 I would like to reset DB2INSTDEF and make it not show up when I do 'db2set -all' (other than dropping and recrea...
I'm looking for the db2 equivalent of T-SQL's: INSERT INTO People (Surname) VALUES ('O''Hara'); ...
in DB2 while executing a query i got SqlException: DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016 does someone knows what this code means? ...
Hi, I have a specific DB2 query, and I would like to execute this query using criteria. The query: SELECT sum(units) as volume, location_id, aged FROM ( SELECT units, location_id, CASE WHEN daysinstock < 61 THEN 'NOT_AGED' WHEN daysinstock < 91 THEN 'AGED' ELSE 'OVER_AGED' END AS AGED FROM STOCK_T...
I am using DB2 v9 on LUW. I have a column defined like this: "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER), I would like to know the best way to determine what the next value will be for the ID column next time a record is inser...
I'd like to use JPA over JDBC for a new application. I'm strictly using Named queries and basic CRUD methods of JPA Entity manager, which allows me (with the help of Hibernate, or any other JPA implementation) to extract all SQL native queries that will be performed on the database. With this list of static queries, I understand that I c...
I am from a MSSQL Server background and tried to search to see if there was a way to perform a trusted connection in DB2 similar to how you do that in MSSQL? I basically do not want to have a user name and password in the connection string and have the "runas" user have that information. Thank you for any help! UPDATED .NET with the IB...
situation: We have a web service running on tomcat accessing DB2 database on AS400, we are using JTOPEN drivers for JNDI connections handled by tomcat. For handling transactions and access to database we are using Spring. For each select system takes JDBC connection from JNDI (i.e. from connection pool), does selection, and in the end ...
I am writing a C program in Unix System Services on a z/OS mainframe. One of the requirements is to get a sequence number from a DB2 database residing on the same mainframe. Not having DB2 Connect available, I'm wondering what my options might be. I can open a socket on port 50000 (the default DB2 port), but, from the IBM documentation I...
We have a view that, without constraints, will return 90 million rows and a reporting application that needs to display paged datasets of that view. We're using nhibernate and recently noticed that its paging mechanism looks like this: select * from (select rownumber() over() as rownum, this_.COL1 as COL1_20_0_, this_.COL2 as...
Why doesn't this work in IBM Data Studio (Eclipse): IF EXISTS (SELECT 1 FROM SYSIBM.SYSVIEWS WHERE NAME = 'MYVIEW' AND CREATOR = 'MYSCHEMA') THEN DROP VIEW MYSCHEMA.MYVIEW; END IF; I have a feeling it has to do with statement terminators (;) but I can't find a syntax that works. Another similar question at http://stackoverflow.co...
I have 1 table with data thus: Col1 Col2 ------- -------- Admin001 A Admin001 B Admin002 C Admin002 C Admin003 A Admin003 C I need to find all instances of Col2 values with 'A' immediately followed by 'B'. 'A' followed by any other symbol does not count. Is there a way to use SQL to accomplish this? Environme...