Top Solutions

I am using DB2 database as a back-end for PB70. I want to concatenate two fields from a table out of which one is string and the other is numeric.

I tried following sqls (Field1 is string;Field2 is numeric) :-

1.select field1 ||...

I have added a new column in the database table and am trying to insert a record into the table using my application. The application throws the following exception:
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703,...

Dear Experts

I'd like to know how one can use the trim function in DB2 for the following problem

select * from table a
where SUBSTR(a.col1, 1, 4) LIKE trim('%' || '0419 '|| '%')
and SUBSTR(a.col1, 5, 8) LIKE (upper(trim('%' || '00004419' ...

Hello Guys,

I'm working on IBM Mainframe, TSO environment. I've got JCL code to apply a function on a DB2 database. The JCL code is as follows:
 

on the Command line in the TSO environment, when I write SUBMIT, I receive the following...

What is the SQL command to list all tables in a database?
What is the SQL command to list all columns in a table?

Hi, I need to retrieve data from a DB2 table daily in a SSIS pacage (SQL Server 2005). I need to filter data by matching a Date column (F1) with Yesterday's date. This Date field F1 contains data like "12/9/2007 12:00:00 AM"

My query statement...

any way i can around this query
select  ICRTDT ,
     substr (cast (int( ICRTDT)+20000000 as char (8)), 1, 4) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 5, 2) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char...

Greetings, experts!

I have a DATE column in my table, and my current session's date-format is ISO. But, in a certain query, I want the date-format displayed as MM/DD/YYYY.  I know I could change the date-format in my SQL session for that query...

This is a Ward transfer code, here is my servlet code, the last 4 queries are creating a weird problem and throwing up an exception when the queries has been done from the database itself. I'm using DB2 UDB 9.1 with Rational Application Developer...

( Product : DBMS -> ORACLE, DB2 and SQL Server )
Hi Experts,

I want to know the difference between the functions SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP in ORACLE, DB2 and SQL Server.?

What does CURRENT_DATE in oracle do ? Is it date...

Well the question looks complicated actually is it possible to have a select within a case which is a part of select thats my question.


Select field a, field b
             CASE                                      
                WHEN...

I am working with a Windows based server and an AS-400 datasource.  I am wanting to create a databse stored procedure on the 400 with which I can A) Pass in a series of approx 25k account #'s, B) Get necessary data for each of those acct#'s, then...

I am using IBM DB2 Database. I need to create a Table with TIMESTAMP columns and i need to give default values for these columns. Following is my script

CREATE TABLE FAX_BACK_INFO(
FAX_BACK_ID INTEGER PRIMARY KEY,
RECIP_NAME VARCHAR(55) NOT...

Hi,
   I am aware that using GENERATED BY DEFAULT option for Identity Columns, DB2 provides me the ability to insert ID(mentioned in the sample code below) of my choice and If I don't provide ID then ID would be created by Server. But when I...

Dear experts

A “schema” in DB2 refers to a qualifying name used to group objects together within a database.

from the above statement what exactly is schema in laymans terms. what objects is the author talking about in the above sentence....

I have a few Oracle SQL statements that I need to run in ISeries DB2. I'm not having much sucess.

Here is the idea. make a backup of Envelope table DatabaseA, empty out the Envelope DatabaseA then copy the content of Envelope Table DatabaseB...

Hello,

I have a stored procedure that starts by doing a MERGE and the clean up old data. The MERGE has an INSERT statement for the NOT MATCHED condition and an UPDATE statement for the MATCHED condition. Is this scenario, I would expect to...

The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time. (Error 3197)

I'm new to db2. I have the free version of it and what I'm trying to do is create a table that has auto incrementing row in it for the primary key.

Also do any one have any good sites for information out side of ibm.com.

Thank you,

thomas

Hello.

I have been experiencing some problems with the IBM Data Server .Net Provider for Framework 4.0 and I would appreciate whether someone could provide some assistance.

I have been developing a web application using Visual Studio 2008...

I'm confused. I've now moved into VisualBasic.NET and I'm trying to use IBM.Data.DB2.iSeries.iDB2Connection.
Does IBM.Data.DB2.iSeries.iDB2Connection use ODBC?

Help file titled: "IBM DB2 UDB for iSeries .Net Provider Technical Reference"
......

When i am trying updating/inserting data in table i am getting below error message

DB2 SQL error: SQLCODE: -290, SQLSTATE: 55039, SQLERRMC: null
 Message: Table space access is not allowed.

I have been working with DB2 Connect PE on an Windows XP 64 bit system. There were and still are a few quirks but I can get it to run using Visual Studio 2008. I have the 64 bit versions installed and have to use the Local IIS Web Server instead...

Hi all,
I am trying to find empty tablespaces - tablespaces with no tables - in my DB2 Z/OS environment and from my control center. The script that I am using is returning error code -206. The error message and script follows:
------
Error...

After I load a table with these commands:

db2 load from c:\data.txt of del warningcount 1000 messages i:\data.log replace into schema.tablename

db2 load from c:\data.txt of del terminate into schema.tablename

I get this message when...

Hi,

I have a .NET web app on a windows 2003 server that connects to several different databases, including DB2 and MSSQL. The DB2 connection is using IBM's ODBC driver for windows and the MSSQL uses ADO.NET. The problem is that the MSSQL...

I have a Model 800 running V5R2 and looking to get a new Power7 box with 7.1  Mainly the only thing that will be moved over is the home grown software.  Mostly all RPGLE and CLP programs and a few RPG.  We are being told that we can not skip...

Hi.

I am trying to switch from an Oracle 8i to DB2 database and I need to update the Java code to support the DB2 CLOB.  

Here is a sample of the existing code that I am using to write and retrieve the CLOB in the ORACLE 8i database.

...

Hi all,
My environment is Vista 64bit, VMware running Red Hat Enterprise Linux server machine, DB2v9.5 64bit.
While trying to install DB2 in this linux machine, I am getting a "The required library file libstdc++.so.5 is not found on the...

Hi All,

Just now i downloaded DB3 8.1 personal from IBM. when i am trying to create a connecting to that i am getting this error below. how to solve this problem?

IWAS0126E Problems encountered while testing to establish connection Con1....

it says how can i clear and incrrease it it plz give steps
thanks
bobby

SQL0964C  The transaction log for the database is full.  SQLSTATE=57011

I like to use inner join and outer join among multiple tables on UDB...
For this, I wrote below, but it basically picking up too many duplicated records. basically I do not like to have duplicated entry from orders_history, just add 4 external...

I am trying to connect to DB2 Database from a VBScript.  The DB2 is installed on my windows 2000 machine that is where I am developing this VBScript.  The following is the VBScript along with the error message.

DB2 Version:
Database server   ...

I recently replaced a computer for one of my users. and now when I run their Iseries Navigator when i click on basic operations and printer output I get a message unable to connect. I have alot of machines that are running Iseries navigator with...

when i am trying to crate nick name (using federated system creating nickname from as400 (os/400) to iDB2/NT 8.2.1f advise its urgent

SQLSTATE: 22003

Explanation: The numeric value received from the AS was out of the supported range.
...

Greetings, I have a fresh DB2 9.5 client installed so I can connect to a DB2 database on a remote server. One of the steps in the DB2 documenation is telling me I need to catalog the DB2 database from the machine the client is installed on,...

What is the equivalent of PATINDEX or CHARINDEX in DB2 SQL.
Please give an example

Thanks

I am working on a tool to issue queries to DB2 on an AS/400 (iSeries) system.  http://sourceforge.net/projects/sqlqueryagent

Is there a way, using SQL, to:
1.  List all the databases on that instance of DB2
2.  List all the tables in a DB...

[IBM iSeries running AS400]

I created the following stored procedure:

=======
CREATE PROCEDURE TBLNAME.proc_test (IN  p_custnum VARCHAR(8),
                                                             IN  p_zipcode CHAR(5),
             ...

I am a Mainframe developer. I am INSERTING rows in a table through a COBOL Application program.
The program is abending with U4094 after processing some records.The 'DISPLAYS' inside the program reveal SQLCODE:90M, SQLSTATE:57011.
As per this...

We are receiving this error from our development database server whenever our web applications attempt to access it (DB2 SDK 8.2.7)  or when we attempt to build a stored procedure. Any ideas about what might be causing this? Thanks in advance for...

Hi, I'm new to AS/400 and DB2 (we're currently at ver 4.5)

I've got a C# application that uses an ODBC connection to retrieve data from a DB2 database.   When running the application I get the following error:  [OdbcException: ERROR [HY000]...

hi everbody

   before some months i installed the DB2 9 on the windows server 2003. Initially it was working properly. But after that  i have not started it for so many days.But today when i tried to start it. i got the following error. At the...

I found strange things when using timestamp funtion in writing a query for db2 sql that had the following clause in the sql statement
AND   timestamp('2009-06-01','00:00:00') <=  startdatetime AND   StartDateTime < timestamp(date('2009-06-30') +...

Greetings,

I have an old 32bit Win 2003 Server that has SQL2000 on it.  This server has a linked server defined which points to a iseries db2 database (jdeEdwards tables).  We have some DTS packages that extract data from the linked server...

I have to date fields stored as integers example 1080101 is equal to 01/01/2008 I need to see the difference in days
I am doing this in SQL inside iSeries navigator.
I already do some string manipulation to get the date into 'CCYY-MM-DD' format...

So I had a customer complain that their DB2 Express-C database (version 9.0) wouldn't talk to them any more.  When I attempted a direct connection to the database, I found that it was suffering from 'unexpected system error'.  

So I attempted...

I keep getting SQL 57011(log full) and 54001 (stmtheap full) errors.  We are running multiple java apps that connect to the database.  We are using DB2 Express-c 9.5.  Let me know what other information you may need.

Here is my current dbm...

The DB2 default value for BLOBS is 1 MB. The maximum size of BLOBs are 2GB.

What problems are there in creating all BLOB columns with the max size of 2GB?

Hi,

I was doing some maintenance on a DB2 COBOL program. The program has a very basic cursor which is used to fetch from a table. The OPEN is a basic one too (i.e. no additional parameters).  The FETCH command is executed such that it fetches...

WE created user ids and groups on the Unix machine that hosts DB2 database.
We wereplanning to grant permissions to tables and stored procs to groups only , not to specific ids.

Our DBA told us that while we can do it for stored proc we can't...

Hi,

I'm new to DB2.  I'm trying to create the following stored procedure for my DB2 database:

CREATE PROCEDURE audit_event_remove_sp (IN v_cutofftime TIMESTAMP)
    RESULT SETS 1
    SPECIFIC AUEVREM
    LANGUAGE SQL...

Hello Experts
I've been asked to identify the root causes for the following warnings in DB2:
MESSAGE : ADM10501W  Health indicator "Monitor Heap Utilization"
          ("db2.mon_heap_util") breached the "upper" warning threshold of "85
      ...

hi guys

I have a store proc like this
>>>>>>>>>>>>>>>>>>>>>>
    CREATE PROCEDURE  RETRIEVE1600
   (
    IN I_DOC_FEE_CODE               CHAR(4),
    IN I_EFF_START_DATE             DATE,
    IN I_EFF_END_DATE               DATE,
     ...

Is there any equivalent of DBMS_OUTPUT in Oracle or PRINT in SQL Server for DB2 or do I need to jump over my head again for this sort of thing?

Hello,
I'm trying to load thousands of records extracted from an EXCEL spreadsheet to a DB2 database (version 8.2), with the date format in mm/dd/yyyy. I did a test as followings:
-------------
db2 "insert into tab1 values (1,...

Hello expeerts,


I am building a .net application on a windows server andrquired me to connect to a DB2 database using the follwoing connection string;-
 TCP/IP:...

Hello

I work in one of the largest financial institutions in Denmark, in BI/Datawarehouse development. Currently we use Datastage XE as ETL tool. The licenses are soon to expire. I participiate in an evalution process, where the options are to...

I would like to use a flag called end_of_month in my date dimension table and want it to be of type boolean.
However,DB2 does not permit this.
What is the best way to work around this.
Thank you

i am trying to run a select count(*) from dim_order and its giving me reason code 3 error can u plz advise why and aht should i do to rectify it?
SQL0668N  Operation not allowed for reason code "3" on table
"DB2ADMIN.DIM_ORDER". ...

I have a client the is using ODBC to import/export shipping information from the UPS Worldship.  This has work for them for years, until recently we had to replace their AS400.  They went from a 170 V4R5 to another 170 V5R1.  Only the user...

I'm wondering what I can code in DB2 so it's something like:

BETWEEN '2007-02-04 00:00:00' AND 'TODAY'S DATE'

Hi,
i have two tables example table1 and table2.
in table1 i  have amount, timestamp and Application number columns and in table2 i have departments,status and Application Number
so i need query that should join two tables by application...

When trying to delete parent objects by using a bulkupdate the children the childrens will not be removed. THis results in the following exception.

DB2 SQL error: SQLCODE: -532, SQLSTATE: 23504, SQLERRMC: CONTRACTSTATUS.FKED672F84341AC4...

I am taking a database class and the instructor wants us to download iSeries Navigator.

Where can I download this from?

Is this the equivalent to Microsoft SQL Management Studio?

Problem with DB2 DATETIME data retrieved and placed in a vb.net  string variable, then attempting to convert back to datetime in an update query.

Example:

A Datetime column has data that in a DB2 table.  When retrieved and placed in a...

Hi Folks,

I'm looking to perform an UPDATE statement whilst incorporating an INNER JOIN in DB2.  The SELECT statement is dead easy:

select isupv from e3sitm i, e3ssrc v                              
where i.ivndr = v.vvndr and i.isubv =...

1)ONE OF MY TABLE IS LOCKED HOW CAN I SEE WHICH TABLE IS LOCKED
2) HOW CAN I UNLOCK IT
THANKS
bobby
Database server        = DB2/NT 8.2.1
 SQL authorization ID   = DB2ADMIN

I have seen several examples on how to do an Update in DB2 using a sub query such as:
http://www.experts-exchange.com/Database/DB2/Q_24787580.html
And I believe I have my sql coded correctly.  But when I execute it, I get this error:

THE...

I am having a problem with a simple select statement when the fields are null.
My input parameters are variables with an i in front in my code below.  If I send in '' for a varchar input parameter value, I do not want it used in the query...

Good Morning,

I have a database that is in the "backup pending state" I am aware that to correct this I can perform a manual backup from the DB2 command line. Is there any other way to change the status from Backup Pending? This is a very...

I'm trying to use commit and rollback in my stored procedure.  I insert records into two tables but I only want to commit them if they both suceed.  The Rollback is not working.  The insert fails on the second table but the record is still added...

I need to obtain the TOTAL disk space (ASP), USED space and FREE space on an AS400.

I just need the AS400 command and also how to enterpret the number displayed. For example, if the total disk space shows: 5000000 how do you convert that to MB...

Hi Experts,

I need to make a NULL Column to NOT NULL Column in db2. Iam using IBM DB2 version IBM DB2 v8.1.7.445.
I have writted query

ALTER TABLE ORDER_QUEUE_HISTORY ALTER COLUMN QUEUETIME_START SET NOT NULL

but its not working. Its...

I'm getting error "Invalid parameter number. SQLSTATE=S1093" when I
attempt to ADD or modify rows within the Control Center (open table
option). However, using the command editor within Control Center to
run SQL statements works perfectly....

Greetings,

I noticed on my RHES4 linux system running DB2 8.2 that there are 20 db2sysc processes running, and I also noticed there are 20 users running a desktop application on their PCs that connects to the DB2 database on the server from...

Hi :

  I am trying to get the query to not timeout. The query returns a result set which is used to display data in the grid. I have been told to set the SQL_ATTR_LOGIN_TIMEOUT = 0 for  the connection but I am not sure how. My Intellisense...

I want to create a select clause that have a column always has null value. Please show me how to do that with DB2.

Ex:

SELECT NULL AS nullColumn, * FROM TBL_A


Thanks so much!

I keep getting the following error:
[IBM][CLI Driver] SL1032N No start database manager command was issued. SQLSTATE=57019

I tried starting the database, and I still get the above error.

Exacly how am I suppose to start the database, and...

1) When we have % and _ in data of column in table, how do we search for these values using wild card search?
2) What I mean is:
SELECT C1 FROM T1 WHERE C1 LIKE '%_AB%_C';
In this case first %_ should be treated as characters of data and...

After restoing latest production backup onto QA environment, we are getting below error message when inserting data into table

com.ibm.db2.jcc.c.SqlException: An error occurred during implicit system action type "7".  Information returned for...

Hello,

I am trying to DataBind a PictureBox with a stored BLOB image in a DB2 database.

I am able to bind the image using a Binding object as seen in the code below.

My problem is the datasource HasChanged() property is always true even...

Hi,
I want to pad the fields while selecting them in the query. The query should look like -

SELECT PAD(<field name>,10,"0") from <table name>
where <condition>

So if the value of the files is 12345 the query should return 0000012345....

Could you please let me know the equivalent of SET ROWCOUNT in DB2?

Is there an existing function in DB2 SQL, to convert date format from '10/24/2008' to '2008-10-24'
AND
is there an equivalent to DATEPART('yyyy', MyDate) in DB2 SQL
Thanks

I'm running Tivoli Storage Manager v6.2.3 and every evening I'm getting "Database Cache Hit Ratio is too low.       89.8 < 98       Increase bufferpool size value."

However all searches for solutions refer to TSM v5.x when it had a custom database. The...

I've tried several commands to do a describe for a table.  I can't find one that works.  I've tried using DESCRIBE TABLE schema.tablename and it doesn't work.  What I'm I missing?  This should be simple, right?  

I am getting a sqlcode -102 on a clob in db2.

Here is the error message

SQLException: DB2 SQL error: SQLCODE: -102, SQLSTATE: 54002, SQLERRMC: 'P6XRzpUGux3ZXWhbjqGub9RF2enKsFikI1Ee0B6yAjv98TdHDvE6K2imLMAKk0BLgnXTI
Apr 18, 2007 11:42:45 AM...

Is there a way to setup a collection service and/or report that will provide object lock information for objects that generate contention errors?   We have a program that runs unattended that periodically encounters an allocation issue for a...

Hello,

how to grant a user a permission for a certain database in db2?

Thanks very much for your kind attention,

lilyyan

Hi gurus,

I backuped a db2 database in windows, now I need to restore it in linux.

Could some expert here tell the steps for restore a db2 database in linux?

Thank you so much for your reply,

lilyyan

I'm using Web Intelligence (Business Objects XI 3.1) to build a query where I need to keep only the Max Comment Date for each Loan Number (need to remove duplicates).   What is the best way to go about this?  

I'm told I should use custom sql...

Hello,

i'm using DB2 control center. my pc was shut down one time today. now i tried to open a table in control center, i got an error
==============================================================

[IBM][CLI Driver] SQL1032N  No start...

How do I do an if-else statement in AS400/iSeries??
In SQL Server, I have:
if whatever Begin
 ..
END
Else If whatver2  Begin
 ...
Else
  ..
End If

I probably can use Case but how do I the above in AS400. (For Begin I need to use THEN)

When I run the first stored procedure I'm getting the error

ERROR [42884] [IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "REPORTSAMPLE" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884

<code>

SET...

Hello,

Over the past 48 hours we have been victim of an SQL injection attack.  Our site is hosted on apache for http and Tomcat for JSP applications.  Our backend database is DB2 V8.1.

This is what I see in the localhost log file from...

//Doesn't look like DB2 supports the from clause in update statements.

//FOR EXAMPLE:  I have 2 tables defined.  
//The customer_input table will be used to update the customer table.
//customer base table
create table customer...

i am getting this error, can anyone help me out with the exact solution for the below reason code

ERROR:  ([IBM][CLI Driver][DB2] SQL0904N  Unsuccessful execution caused by an unavailable resource.  Reason code: "00C900A0", typ
e of resource:...