How to display source of DB2 SQL UDF
Is there any way to see SQL UDF body in DB2 control center? ...
db2
How to handle stale connections?
Hi Friends, Ours is a J2EE app, using Struts-EJB-Hibernate on Websphere 6.1 over Mainframe/DB2 backend, that was recently moved to production. We are getting stale connection exception when the user login to the application first time or some times this exception occurs intermittently. on the second try the user able to log in to th...
Awk appears to disconnect my DB2 session when piping
Hello. I'm attempting to run the following command in Korn Shell (ksh): set -A INDEXES `db2 "describe indexes for table ${TABSCHEMA}.${TABNAME} show detail" | awk '{print $1"."$2}'` What I'm attempting to achieve is place a list of the indexes over a particular table into an array which I can later iterate through. The problem is, w...
copying a table from one database to another
I am trying to archive some of my tables into another database on the same server. However the INSERT INTO...SELECT...FROM gives me an error (SQLSTATE=42704) on build. The table exists in the second database. Can anyone help with this? ...
Best Approach for Cloning database records using SPs
Hi, I have to write code to clone a database entry with associated data in other tables and assign it a new ID. Simplified I have a MAIN Table with a key of ID and sub table say SUB1 with FK of ID and multiple records for each entry in MAIN. I want to copy the data for a specific ID in MAIN to new records updating the ID to a new value...
Writing the content of a local variable back to the resultset column?
Hello! Is it possible, by using a stored procedure, to fetch a column value from resultset into a local variable, manipulate it there and then write it back to resultset column? The column datatype is integer. How would the syntax to write the value back look like? Thanks in Advance... :-) ...
DB2 Relational Tables
Is it possible to create a database such that there are 2 tables across 2 different schemas within the database who reference each other? I would like to clarify my question with an example. Consider the tables, EMPLOYEE (empID, empName, deptId) and DEPARTMENT (deptId, deptName). We can impose a foreign key constraint on the EMPLOYEE.d...
NHibernate - Mapping ID to a DB2 Identity
I'm newish to NHibernate, and am attempting to wire up to a DB2 table through the ISeries provider. The table has a BIGINT primary key that is auto generated as an identity. I've tried several values for the generator property of the id in my mapping file, and haven't had any success. The table def looks like so (field names changed):...
C / C++ program connecting to 32-bit DB2 and 64-bit DB2 simultaneously
Is it possible to successfully author a C / C++ program in *IX operating systems that operates on both 32-bit and 64-bit DB2 implementations simultaneously? The exact requirement is to read from a 32-bit DB2 database and write into a 64-bit DB2 database. ...
In DB2 Display a table's definition
Hello everybody I am learning DB2 and would like to know how to see a table's characteristics after I create one. Similar to the EXPLAIN TABLE command in MySQL. Thank you. ...
What are the pros and cons to writing DB2 stored procedures in SQL versus Java?
With newer versions of DB2 you can write stored procedures in SQL or you can create procedures in Java (or other languages) that can then be added to the database and called just like SQL procedures. I'm wondering what the the pros and cons of each method are. I'm specifically interested in comparing those two types of procedures, not ...
How to limit user access at database level in Hibernate
The App I need to implement a web app that will be used by different users. Each user has different privileges on various tables, e.g. User A can see fields 'name' and 'address' from the table Student User B can see fields 'name' and 'phone number', but not 'address' from the table Student User C can see and modify all fields me...
Check if exists within SQL CASE statement
I am trying to update a column in table a based on whether a different column in the table is in a set of results from table b. Currently variations on: update a set a.field1 = case when exists ( select b.field2 from b where b.field2 = a.field2 ) then 'FOO' else 'BAR' end are not running. Any ideas how to do this for a ...
Why am I getting this SQL/DB error?
Hey everyone, I am trying to run a simple SQL statement with DB2 and am having a few problems. I would like to have a single script in a txt/db2 file and have the engine process all of the commands Here is the script: CONNECT TO MYDB CREATE TABLE PERSONS( PID SMALLINT NOT NULL, NAME VARCHAR(20) NOT NULL ) TERMINATE When ...
Description of columns in a DB2 table
Hi, How can we know the description of every column in a table(DB2) through SQL? My data base is DB2. Thanks in Advance ...
DB2 multiple column index issue
If I have a multiple column index(a,b,c) and one separate index d then my query is using b,a,c,d order whether both the index will be chosen? Whether index will be choosen or not for this query? ...
How to extract numerical data from SQL result
Suppose there is a table "A" with 2 columns - ID (INT), DATA (VARCHAR(100)). Executing "SELECT DATA FROM A" results in a table looks like: DATA --------------------- Nowshak 7,485 m Maja e Korabit (Golem Korab) 2,764 m Tahat 3,003 m Morro de Moco 2,620 m Cerro Aconcagua 6,960 m (located in the northwestern corner of the province of Mend...
How to delete all non-numerical letters in db2
I have some data in DATA column (varchar) that looks like this: Nowshak 7,485 m Maja e Korabit (Golem Korab) 2,764 m Tahat 3,003 m Morro de Moco 2,620 m Cerro Aconcagua 6,960 m (located in the northwestern corner of the province of Mendoza) Mount Kosciuszko 2,229 m Grossglockner 3,798 m What I want is this: 7485 2764 3003 2620 6960 2...
BIRT 2.2 dynamic schema name
Is it possible to make the schema name dynamic in a BIRT query. I tried this: SELECT CURRENT DATE AS DATE, (CASE WHEN DAYOFWEEK(CURRENT DATE) = 1 THEN 'SUNDAY' WHEN DAYOFWEEK(CURRENT DATE) = 2 THEN 'MONDAY' WHEN DAYOFWEEK(CURRENT DATE) = 3 THEN 'TUESDAY' WHEN DAYOFWEEK(CURRENT DATE) = 4 THEN 'WEDNESDAY' WHEN DA...
Dropping a schema and all of its contents in DB2 8.x
How would I drop a schema and all of its contents, using SQL, in DB2 8.x without knowing what the content is? ...