Questions tagged [db2]

DB2 is a family of database servers developed by IBM. Supported platforms for DB2 servers are z/OS (formerly OS/390), Linux, UNIX, and Windows (referred to collectively as DB2 for LUW), and IBM i (formerly OS/400 or i5/OS). Originally designed as a relational engine, IBM has expanded DB2's capabilities on some platforms to include native XML support and an RDF-compliant graph store. When posting DB2 questions, please specify the platform and version.

Filter by
Sorted by
Tagged with
114votes
3answers
209kviews

Difference between CLOB and BLOB from DB2 and Oracle Perspective?

I have been pretty much fascinated by these two data types. According to Oracle Docs, they are presented as follows : BLOB : Variable-length binary large object string that can be up to 2GB (2,147,...
user avatar
96votes
10answers
148kviews

Equivalent of LIMIT for DB2

How do you do LIMIT in DB2 for iSeries? I have a table with more than 50,000 records and I want to return records 0 to 10,000, and records 10,000 to 20,000. I know in SQL you write LIMIT 0,10000 ...
user avatar
  • 5,781
67votes
3answers
47kviews

Relationship between catalog, schema, user, and database instance

To compare databases of different vendors (Oracle, SQL Server, DB2, MySQL, and PostgreSQL) how can I identify any object uniquely and do I need a catalog? For instance, In Java's DatabaseMetadata I ...
user avatar
  • 1,921
61votes
15answers
162kviews

How to check db2 version

How to check db2 version on Z/OS using only SQL commands? Thanks, Melita
user avatar
  • 7,539
53votes
11answers
157kviews

SQL Statement with multiple SETs and WHEREs

I am wondering if this is a valid query: UPDATE table SET ID = 111111259 WHERE ID = 2555 AND SET ID = 111111261 WHERE ID = 2724 AND SET ID = 111111263 WHERE ID = 2021 AND SET ID = 111111264 ...
user avatar
47votes
14answers
262kviews

DB2 Query to retrieve all table names for a given schema

I'm just looking for a simple query to select all the table names for a given schema. For example, our DB has over 100 tables and I need to find any table that contains the sub-string “CUR”. I can ...
user avatar
  • 2,681
47votes
19answers
300kviews

How to view DB2 Table structure

How to view the table structure in DB2 database
user avatar
  • 1,295
47votes
1answer
127kviews

Trying to get the average of a count resultset

I have the following SQL:(bitemp) SELECT COUNT (*) AS Count FROM Table T WHERE (T.Update_time = (SELECT MAX (B.Update_time ) FROM Table B WHERE (B.Id = T.Id)) ...
user avatar
  • 8,330
45votes
8answers
199kviews

How to find substring inside a string (or how to grep a variable)? [duplicate]

I'm using BASH, and I don't know how to find a substring. It keeps failing, I've got a string (should this be an array?) Below, LIST is a string list of database names, SOURCE is the reply, one of ...
user avatar
  • 2,889
41votes
5answers
64kviews

Does DB2 have an "insert or update" statement?

From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed. My code now does a select and if no result is returned it does an insert. I really don't like ...
user avatar
40votes
4answers
109kviews

How to AUTO_INCREMENT in db2?

I thought this would be simple, but I can't seem to use AUTO_INCREMENT in my db2 database. I did some searching and people seem to be using "Generated by Default", but this doesn't work for me. ...
user avatar
  • 4,748
40votes
13answers
94kviews

How to connect Python to Db2

Is there a way to connect Python to Db2?
user avatar
37votes
8answers
9kviews

Connect PHP to IBM i (AS/400)

I've got an upcoming project wherein I will need to connect our website (PHP5/Apache 1.3/OpenBSD 4.1) to our back-end system running on an iSeries with OS400 V5R3 so that I can access some tables ...
user avatar
36votes
5answers
175kviews

Create a copy of a table within the same database DB2

Is there an easy way to copy a table to the same database of course with different name. I tried some of these listed below, db2 "CREATE TABLE SCHEMA.NEW_TB COPY AS SELECT * FROM SCHEMA.OLD_TB WHERE ...
user avatar
35votes
5answers
110kviews

insert multiple rows into DB2 database

I want to insert multiple rows into a DB2 table. I have a query that looks like this insert into tableName (col1, col2, col3, col4, col5) values (val1, val2, val3, val4, val5), (val1, val2, val3, ...
user avatar
  • 1,615
35votes
7answers
43kviews

Select Query by Pair of fields using an in clause

I have a table called players as follows: First_Id Second_Id Name 1 1 Durant 2 1 Kobe 1 2 Lebron 2 2 ...
user avatar
34votes
2answers
26kviews

DB2: Won't Allow "NULL" column?

Part of a complex query that our app is running contains the lines: ...(inner query) SELECT ... NULL as column_A, NULL as column_B, ... FROM ... This syntax of creating columns with null values is ...
user avatar
  • 2,486
32votes
4answers
83kviews

How does one escape an apostrophe in db2 sql

I'm looking for the db2 equivalent of T-SQL's: INSERT INTO People (Surname) VALUES ('O''Hara');
user avatar
  • 30.1k
31votes
3answers
327kviews

SqlException: DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null

What does sqlcode -302 means? Where do i get the sqlcode definitions? Please advice Thanks,
user avatar
  • 941
31votes
2answers
116kviews

Declare a variable in DB2 SQL

Does anyone know how to run the following SQL Server code in DB2? I am converting SQL Server scripts so that they will run on a DB2 system and am having some problems wrapping my head around the ...
user avatar
30votes
2answers
101kviews

Adding a static value to the results of an SQL query

I'm wondering if there is a way to accomplish this with an SQL query. I have a table, lets call it "LISTOFTHINGS" that has two fields of interest "ID" and "NAMEOFTHING" What I want to do is ...
user avatar
29votes
6answers
221kviews

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 - db2inst1 root@VO11555:~# . ~db2inst1/sqllib/...
user avatar
  • 3,671
28votes
5answers
63kviews

db2 SQLCODE -668 when inserting

While I was inserting data into a table (db2), I got this error: Message: Operation not allowed for reason code "7" on table "ELSAG.ICGR1106".. SQLCODE=-668, SQLSTATE=57016, DRIVER=3.50.152,... ...
user avatar
28votes
3answers
73kviews

How do you get values from all columns using ResultSet.getBinaryStream() in jdbc?

How do I to write an entire table to a flat file (text file) using jdbc? So far I've attempted the following: Statement statement = connection.createStatement(); ResultSet result = statement....
user avatar
27votes
8answers
96kviews

Description of columns in a DB2 table

How can we know the description of every column in a table(DB2) through SQL? My data base is DB2.
user avatar
27votes
6answers
142kviews

Exporting result of select statement to CSV format in DB2

Is there any way by which we can export the result of a select statment to CSV file, just like in MySQL. MySQL Command; SELECT col1,col2,coln into OUTFILE 'result.csv' FIELDS TERMINATED BY ',' ...
user avatar
  • 34.3k
26votes
1answer
23kviews

Simple DB2 Query for connection validation

I'm looking for a simple DB2 query that can be used to test if a database connection in pool is still valid. It needs to be a generic query that would execute regardless of which databases exist. ...
user avatar
24votes
7answers
61kviews

DB2 Comma Separated Output by Groups

Is there a built in function for comma separated column values in DB2 SQL? Example: If there are columns with an ID and it has 3 rows with the same ID but have three different roles, the data should ...
user avatar
  • 2,444
24votes
4answers
75kviews

How do I use DB2 Explain?

How do I use DB2's Explain function? -- both to run it, and to use it to optimize queries. Is there a better tool available for DB2? I've built queries before, but the only way I've had to tell how ...
user avatar
  • 781
24votes
7answers
21kviews

Cycle detection with recursive subquery factoring

Oracle SQL can do hierarchical queries since v2, using their proprietary CONNECT BY syntax. In their latest 11g release 2, they added recursive subquery factoring, also known as the recursive with ...
user avatar
  • 17.4k
23votes
6answers
189kviews

How to update multiple columns in single update statement in DB2

I want to update multiple columns of a table in DB2 with single Update statement. Any hint or idea will be appreciable. Thanks.
user avatar
  • 831
23votes
4answers
33kviews

How to get the last insert ID from a table

I want to get the value of the last ID insert in a table. How I can do this?
user avatar
22votes
4answers
34kviews

Query validation using C#

I am looking for a query validator in C#, which allows me to parse the SQL text from a textbox and verify whether it's correct or not before sending it for execution (MS SQL or DB2 queries).
user avatar
  • 2,609
22votes
3answers
49kviews

Is Null Greater Than Any Date Data Type?

I have this query in DB2 SELECT * FROM SOMESCHEMA.SOMETABLE WHERE SYSDATE > @A If the SYSDATE is NULL, would it be greater than any value of @A, assuming that @A and SOMESCHEMA.SOMETABLE.SYSDATE ...
user avatar
21votes
5answers
89kviews

How to identify DB2 port number

I have to make DB2 connection in java using port number. Is there any command in DB2 or any way that can get the DB2 port number? I have not used the default port 50000 while making DB2 connection as ...
user avatar
21votes
6answers
117kviews

How do I connect to a Websphere Datasource with a given JNDI name?

I'm using Websphere Portal 7.0 and creating a portlet with RAD 8.0. My portlet is trying to make a db2 connection to a remote server. I wrote a java program locally to do a basic JDBC connection to ...
user avatar
  • 2,145
20votes
3answers
54kviews

DB2 database in Oracle SQL developer

I've heard it's possible to connect to a mainframe DB2 database with a client like Oracle SQL developer. I've looked on-line and can't seem to find the connector files needed to do this in SQL ...
user avatar
  • 1,615
20votes
1answer
30kviews

How to find table and column in DB2 with tbspaceid tableid specified in error message

I get following error message when trying to insert an object in the database: com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=...
user avatar
  • 4,451
20votes
5answers
205kviews

DB2 Date format

I just want to format current date into yyyymmdd in DB2. I see the date formats available, but how can I use them? http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm....
user avatar
  • 11.6k
20votes
8answers
134kviews

IsNull function in DB2 SQL?

Is there a performant equivalent to the isnull function for DB2? Imagine some of our products are internal, so they don't have names: Select product.id, isnull(product.name, "Internal) From product ...
user avatar
  • 917
20votes
3answers
62kviews

Make a column nullable in DB2 when Data Capture is enabled

I'm using db2 version 9.7* and it seems impossible to make a NOT NULL column nullable in any straightforward way. Unfortunately the solution of using a more developer friendly database is not ...
user avatar
  • 20.9k
20votes
11answers
108kviews

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.
user avatar
19votes
3answers
125kviews

determine DB2 text string length

I am trying to find out how to write an SQL statement that will grab fields where the string is not 12 characters long. I only want to grab the string if they are 10 characters. What function can do ...
user avatar
  • 1,615
19votes
4answers
29kviews

SQL query of multi-member file on AS400

On AS400 in interactive SQL in a 5250 session, select * from myfile returns rows from one member only when myfile has more than one member. How can I get rows from a specific member? Important: in ...
user avatar
  • 1,501
19votes
2answers
48kviews

Cannot use group by and over(partition by) in the same query?

I have a table myTable with 3 columns. col_1 is an INTEGER and the other 2 columns are DOUBLE. For example, col_1={1, 2}, col_2={0.1, 0.2, 0.3}. Each element in col_1 is composed of all the values of ...
user avatar
19votes
2answers
60kviews

decimal(s,p) or number(s,p)?

recently, while working on a db2 -> oracle migration project, we came across this situation. the developers were inadvertently creating new table structures using decimal(s,p) columns. I didn't ...
user avatar
  • 1,998
18votes
2answers
37kviews

How can I set the current schema for DB2 using Hibernate/JDBC?

I used to use currentSchema=MYSCHEMA; in my JDBC URL connection, but the version of DB2 we're using no longer supports that, showing the error 'The "currentSchema" property is not allowed on the ...
user avatar
18votes
3answers
57kviews

Way to decrease column length in DB2

Is there a way to decrease the column length in DB2? Say I have a table temp with column col1 defined as VARCHAR(80). I want to reduce it to VARCHAR(60).
user avatar
  • 15.4k
18votes
4answers
65kviews

Is there any opensource DB2 client? [closed]

I am using db2 version 7 , i need an windows application (similar to MSSQL) to do the database operations.
user avatar
  • 1,294
18votes
4answers
47kviews

SQL Developer "disconnected from the rest of the join graph"

I have the following SQL: select <misc things> from pluspbillline left outer join workorder on workorder.siteid=pluspbillline.siteid and workorder.wonum = pluspbillline.refwo and ...
user avatar
  • 949

15 30 50 per page
1
2 3 4 5
240