Hot Solutions

How do I use the CURRENT_DATE function in DB2 to pull data for the past 30 days?
I am tracking new accts opened and want a report automated that will run daily and still show every date in the month. For example, since today is 2/22/12, I want...

Hi,

  We were on Db2 UDB 8.2. Recently migrated to DB2 9.7

  There are a number of places where we have used this TIMESTAMPDIFF  function, as follows.

 

But after migrating to 9.7, when we used this function, it was returning a zero...

Hi,

  Can somebody be able to provide an example of using ADO 'UpdateBatch'?  I am trying to insert multiple records into DB2 database.

Can I create a recordset with multiple rows in it and use the 'UpdateBatch' method. We are using...

We are on iSeries and use the JD Edwards EnterpriseOne Software. We are in the process of going live with a new version we just upgraded to: EnterpriseOne 9.0.

We are in the process of doing the data only upgrade right now. We are running the...

Hi all, I have a numeric field wich is supposed to represent a date. Somehow a developer thought that a mixed form of 'ddmmyyyy' and 'dmmyyyy' was ok to represent a date.

So to compare dates I want to create a query using the DATE, SUBSTR,...

Hi Everyone,
I have a question on loading data from an IXF file into a DB table(s).
The IXF is a backup from one DB and contains several years worth of data from a source. I want to load only the "new" records to the target, ones where a...

Hi All,

I am trying to use the CHGOWN command to change owner on all objects in a library. My user profile has *ALLOBJ access. However, I am not able to change the ownership on stored procedures that exist in this library. The error message...

My BPCS erp system is being upgraded from 6.1 to 8.3.4 and in the process some of the DBCS fields are now unicode. The base code page is 037.

I want to use ODBC (preferrably client access) to insert records to the table and read back to ensure...

Hi all,

I have a number of identical tables, only the names are different.
I need to select several fields from 3 of these tables based on some conditions and order them by date field.
All I came up with is this monster (below), and it...

Is there a way to just install the DB2 Control center on a workstation?  I do not need the database just the control center to access the DB2 server

Can anyone point me the URL?  Or do I have to install the whole thing?

Hello,
I want to create an application for tracking orders of a cloth designing company using java,javadb and netbeans. The company makes tailor made wedding outfits for brides and grooms they want to keep track of the orders. i want to know the...

Hi,

I'm trying to export data from DB2 to a CSV file using an SQL query as such:

export to {FILENAME} of DEL, SELECT {column_list} FROM table

However, it seems like any columns of type CHAR end up getting padded by white space when...

Hi there, can anybody help me with an SQL problem i am having on our iseries?

I am trying to update the std cost of a range of parts.

The file is TSTT2F1/INP60, and the Stockroom is STRC60, the Company is NC and the Standard Cost is SCSU60....

How do I use the CURRENT_DATE function in DB2 to pull data for the entire past month on the first day of a new month?
This job runs flawlessly daily with the piece of code below, but on the 1st of the month it returns no results:
ACCT_OPEN_DT...

Hello Experts,

i am trying to call DB2 stored procedure from a query tool - sqldbx...

1 input parameter and all others are output parameters..

i am getting some error:

here is my code:


DECLARE @AD_USR_ID VARCHAR(64)
DECLARE...

I've linked our iSeries to our SQL server.  I've created a view with this:

Create View View_name as
 
SELECT *
 FROM [10.1.1.253].[S10778CE].[AIR400].[AGENT]

Where would I find this view?

Hi,

We have a executedataset method where we pass store procedure name, store proc input parameters as dbparams

Executedataset(storeprocedure name, dbparam).

But while trying to execute the store procedure which has a dynamic query in...

Hello Experts.
Looking for help with importing data into DB2 table.
Character delimited (| -pipe) source file has strings like:
"word1" word2
in some columns. This creates an error:
SQL3114W  Some data following ""word1"" in row "34479" and...

Hi Experts,
I have a DB2 table like this:

JOB | Sequence | TXT
123 |       1        | this is an ex
123 |       2        |ample to sho
123 |       3        |w my data.
124 |       1        |one line here
125 |       1        |another tes...

Hi All,

We have a production AS400 server and a Disaster Recovery AS400 server with pretty much the same configurations. Both machines are on V5R4. We have a schema present on both machines which holds MQT's. In production, we have a job that...

I am newbie with IBM DB2 DBMS.

I need to write some commands below.

Somebody please help me?

Thank you very much!


1. Give a set of commands to create a database name TEST1 in schema DB2ADMIN with the following specification

-...

How do I use the CURRENT_DATE function in DB2 to pull data for the past week?
I am tracking new accts opened and want a report automated that will run weekly on Tuesdays showing accounts opened from last week Monday through Sunday. For example,...

after a huge headache and lots of help from this community, i was able to configure my oracle machine to read from the AS400, i know it works because i can connect to the as400 using isql

isql points to the appropriate odbc settings and i can...

Hi all,

I restored a backup to my HADR standby database environment, successfully started standby and the primary about 12 hrs ago, but both Primary and standby are still in localcatchup state? What does that mean in terms of HADR being...

Hi,

 I have the following data znd below, the desired result.
 Basically, the result set is for each CL_ID. If there is a ORIG_RM_ID for a RM_ID, then
this ORIG_RM_ID is the RM_ID for the same client above.

Example : For CL_ID = 123 and...

Hi,
Have an emergency,
need to turn off Health monitoring on  db2 databases  as no reports are running....the guy who normally looks after db2 is out and I have never used this before,,,,any advice please....thanks

Hi All,

I have a DB2 query which I am running on my production server:

 

The table TDV_FNHIST has ~169 million records. The approximate table size is ~17GB. The other table in the sub query is a single row table.

I haven't been able...

Hello all,

I have a show stopper on my hand and I need help ASAP if you can. Here is the error that I get when my application process try to INSERT into a series of tables of interest. The error that I am getting says:

SQL2032N  The "COPY...

We have performed some table conversions on some tables on our iSeries. But we now need to go back and check each table in the LIBs to make sure the new table format shows the new additions...such as new table columns, records, etc.

One of the...

I've to create a table adapter quering tables in an AS400 database using an odbc connection. All works fine but I want to convert a field in decimal format (for example 20120415 ... today date) in a date format (15/04/2012). I've tried cast and...

Hello,

I would like to find the simplest way to bring a data set from AS400 Iseries to Excel.

Please note the perspective must be from excel macro, which means the Excel macro must utilize a connection string that would connect to the...

The company is organized into DEPARTMENTs. Each department has a name, number and an employee who manages the department. We keep track of the start date of the department manager. A department may have several locations.


What meaning the...

Hi all,

While writing my first piece from home, I just got a call that one of my database is receiving  a Just got a SQL1034C SQLSTATE=58031 with the exciting information that The database is damaged.  All applications processing the database...

Greetings,

I am looking for a method to trace SQL code in DB2 databases. We have Java related process that hit a particular DB2 database and we would like to see the SQL code from that process that hits the DB2 database. Please reply back with...

Experts,
Do you know if coldel can include several characters?
DB2 documentation mentions coldel as a single character. In my case the data in input file have all imaginable characters.

Thank you.

Hi,

  I am trying to deploy and run the attached DB2 stored procedure (DB2 z/OS V8). I am able to compile it, but it throws the following errors.

if the input parameters is this:

CALL...

Hi,

 I have this SQL.Basically, i want to display all ODD rows as the left hand section and all the even rows in the right hand section. In doing so, i'm getting blank rows associated with either of odd or even sections.
Example : If the fist...

Actually this is an follow up question to my previous question. I was solving the concurrency issue and used locks to solve it. Following is the stored procedure that I deployed.

LOCK TABLE  PMC.MODEL_UPN IN SHARE MODE;

       SELECT...

Hi Everyone,

I'm trying to design a query/CASE statement that will ignore the case of a field. Example: 'APPLE' and "Apple'.
I would like to combine the following two separate statements into one statement.
WHEN FOOD = 'APPLE'               ...

Hi all,

Is there a quick way to find the reason why a DB2 backup job failed to complete and in a hang state. My job says 78% completed but stopped there for the rest of the day and did ot complete. Any SQl, admin_view that can be used to get...

OK,

Where can I find an explanation about all db2 (from 9.1, 9.5 and 0.7) editions?
Are all editions universal database type?
What is Universal database?
We have bought this product: IBM DB2 Workgroup Server Edition PVU Option. Is this...

Hello,

How can I strip a backup file for having small files?

Thanks

bibi

Hi,

This might be a beginners question. I ran into concurrency issues in my db2 store procedure. I need to generate unique 'REC_NO' for the 'MODEL_UPN' table. However, In our test environment when simultaneous users are using the same store...

Hello All,

Please I need a quick answer if possible with this issue. Why am I getting
[IBM][CLI Driver][DB2/AIX64] SQL0628N  Multiple or conflicting keywords involving the "NOT LOGGED INITIALLY" clause are present.  SQLSTATE=42613” when I...

I have impoted a taable from  text file into Access2010, the table has 60 records.
I need to add a column with the date stamp. I need it to be added to all records even the old ones not only to the new records added. how to do that?

Hi all,

Just a quick quetion. I have tablespace 4K, 16K which I am going to backup individually at the tablespace level. My requirement are that the tablespace and all the tables that it contains be restored to a 32K tablespace.
How can I...

Hi all,

Here's what I have:

Hundreds of tables with identical DDL with only names different,
constructed based on a day-time, so TBL2012041808 is for April 18
2012 8AM data, TBL2012041809 is for 9AM etc.
Each table contains upwards of...

I found the following code to use SQL Server 2005 to query information in my Active Directory structure:

Found at: http://www.bravesoft.com/blog/?p=24

 

This code works from SQLServer2005, but can someone point me to an equivalent...

Hi all,

I have a table with 460+ million plus rows. This table is read "once" a day to retrieve a single maxrownum used for downstream processing. The problem I am having is that this select SQL is taking about 1 hr 10-20 minutes to retrieve...

Hi all,

Is there a way to trace a running or completed SQL back to the user id or the terminal ID that initiated the SQL? We have a situation that we will like to know which of the users or terminals started a application with a sloww running...

I need to search for a list of tables on our iSeries server. What is the command to use to search for a table in a specific LIB?

Thanks

Hello Experts

I am trying to view the DDL of views on an as400, i need to know what tables certain data is coming from on reports.

I also would like to know where the data on generated spool files is being pulled from. Is there any way to...

Hi,
   
   I'm on Db2 9.7.
  i have two databases in my configuration assistant.One points to System test and another points to the Production. I'm trying to do something like this.
 

Is there a way to reference a database schema inside...

Greetings Experts,

We are looking at purchasing a data modeling system for our organization.  I have seen in past posts the most recommended systems are:

PowerDesigner
Oracle Designer
CA ERWIN

However, I have been able to find...

i need to show the records of only the last seven business days.
starting form the selected day.

Hi all,
I am facing a potentially grave IT problem. I need some expert, even OS system help.
Looking at my *NFY and db2diag.log, I am finding error messages that has me very worried. There are several types of errors listed here. Please read...

Hello experts.
Need your help with modifying data in DB2 table.
The data in one of the columns is like in this example:

abc123
abcd4567
1234abc
1234

so, it's mostly four to eight characters. I would like to add heading "spaces" where...

Hi all,

I have a table defined as follows:

CREATE TABLE table (
      DT_WHEN TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL,
      ...
      PRIMARY KEY (DT_WHEN,...)
);

DT_WHEN constraint is violated only if I attempt to insert records at the same...

I HAVE AN APPLICATION RUNNING ON TOP OF DATABASE, ONE OF THE APPLICATION FUNCTIONALITIES IS TO INSERT 10000 RECORD INTO ONE OF THE TABLE. mY QUESTION IS WHICH IS BETTER TO DROP INDEX AND CREATE IT AFTER EACH INSERT OR TO RUN STAT COMMMAND OR TO...

Hi all,

Is there a way, tool, routine or such to change a table schema to a new schema? In the mainframe, we have if we want to change OLDNAME to NEWNAME we can code the following CATMAINT job:

CATMAINT UPDATE
SCHEMA SWITCH(OLDNAME,...

How do I automate "acy_dt between '4/1/2012' and '4/30/2012'" so that every month I don't have to manually change it to say, between '5/1/2012' and '5/31/2012' and so forth?

Thanks.

Hi Experts,

I got a question from an BizzTalk "specialist" to define journaling for him, because BizzsTalk can't insert data into DB2/400 tables without commitment control.

Seems a little odd to me, please can someone (dis)confirm this...

Hi all,

I am having problem creating "SERVER" for DB2 federated replication and I have some questions about the create server parameters. Please note my questions followed by the SQL that I am using and the error message that I received.
My...

Dear all,

I am a DB2 beginning, any good link (easy to understand) that shows:

1) the way to start DB2 (must using command mode, right? any DB2 UI ?)
2) brower and manage DB.
3) secure harden DB
4) Define index, index maintenace. DB...

How can I setup a linked server
(or something) in Oracle so the below
works with my Lotus Notes database ?
---------------------------------------------
select *
from SHOP_ORDER_PROP_LOTUS
where PART_NO = '1008866-901'
union all
select...

Hello All,

I have this error running a particular application and I get this error:

Failed : Dynamic sections check : Dynamic sections in DB2 databases set to 200. It should be at least set to 1000

How do I correct this error? What...

Hi experts, I have a  small simple SQL Server 2008 Database, I need to import it to a DB2 server.   How can we accomplish this?   Is there a wizard like the Import capability of SQL Server 2008.  If not and using command line , can you brief me...

Hi all, I hope all is well in your homes. I do have s set of SQLs that is giving me migraine because the performance is driving me up the wall. The set of SQL is coming from SIEBEL generated SDLs, indexed to dearth, I think, runs from 12 am in...

Hi,
i have two variables which are not passed in sql right now i want to get rid of that variables and pass them direct to sql how can i do that



v= DATE_STRING(CURRENT_JULIAN()-7, YYYYMMDD);

UserVar.DELTA_EndTime = STRING_concat(
    ...

I have ran into a little problem on a older 170 running 45R5 where the FTP server will not start.  Attached is the job log I get when I try to start it.  Also if I try to FTP out to another server I get this:
Exception CPF3CDB occurred when...

Hi,
I am trying to run some stored procedures using DB2 control center, I was hoping I could right click/double click on a procedure in the Procedures view and get an option to execute it but this does not seem to be the case.
i have tried...

I have Oracle but I think this can be any database server.  I have a table with triggers on insert, update and delete.  I want to trigger the trigger without making any changes to the data.  I am thinking that there may be something that UPDATE...

Hi all,

I have a big SQL that I wanted to explain using the DB2 Control Center explain option. After executing for a few minutes, the explain process abended with the following error:

AppName: javaw.exe       AppVer: 141.2004.3.1      ...

Hi all,

I dn't believe this cannot be done but let me ask anyway. I am being told by our vendor support anlyst that and I quote  "...DB restore on a SAN disk in not possible.
It’s not like any filesystem restore where you choose destination...

Hello Team,

Here is the situation, I have two elements

 - A Lotus notes database
 - A data warehouse with some information

I have the SQL formula and Admin access to the DB. I have knowledge on VB programming.


My question is:

 -...

I've been asked to convert our DDS file-creation source into SQL source. In essence this is not a difficult task: create a SQL table to replace a DDS-created physical file, and replace any related logical files with a combination of views and...

I have been tasked with developing a reporting tool that will tap into a set of data that is structured in a manner that I have not dealt with before. I am having a bit of difficulty trying to determine what approach to take.

I am quite...

Hi all,

I have a need for examples on what type of change request document that I can develop to manage how requests from users and clients can use when they request change to DB2. Yes, I can try to come up with one but I will like to see how...

For example:

I have a table with columns:
 Name |  SumWidget1| SumWidget2
Sam   |     100.00      |  350.00

I need a View from the table with columns with the column names SumWidget1 and SumWidget2  in a WidgetTypes column, and their...

I have a SSIS package that queries  AS400 server and eports to Sql Server.
I use .NET PROVIDERS FOR OLEDB\IBM DB2 UDB for iSeries IBMDA400 OLEDB PROVIDER
when I fetch Hebrew text it exports it as reversed string. the localeid is Hebrew Israel....

Hi all,

How does DB2 UDB v9.& determine the last log in a set of logs for rollforward purposes?

Does DB2 v9.7 have anything comparable to the RBA or LRSN that is used sometimes to roll forward to a point in time?

And if DB2 v9.7 have...

I'm looking how to get an ODBC driver for DB2.  I found it on IBM's website but the instructions say I have to install it manually, and of course don't tell you how to do that.  Here is a link to what I found.  Please help!

...

Hi,
I have table which looks like

table1
wc_id email
101    aa@yahoo.com
101    bb@yahoo.com
102    cc@yahoo.com
203    dd@yahoo.com

i want the results as
wc_id email
101    aa@yahoo.com;bb@yahoo.com
102    cc@yahoo.com
203   ...

I am trying to query an EOM DB2 table with date_id for every last day of the month. How can I select the last date_id without manually typing it in?

I tried date_id = max(date_id) and it didn't work.

Thanks.

I need to get a report of all logical files in my database db2 dds 400 version 5.4 that have broken the link with physical files. That is, a report of all logical files that are not synchronized with any physical file.

My server is a P525 with...

I'm inserting a record into an AS400 using a .NET SQL Datasource.  Nothing has changed (at least as far as I know), yet today all I get is the above error.  Seems like I have to cast my parameters?  Enclosed is the code for the SQL Datasource. ...

Hi,
I'm a DB2 newbie , is there a step by step document how to create DB2 database on Linux & UNIX?

Thanks,
Tal

Hi all,

Question: I have a HADR setup - Primary and Standby. Backup is always done on the primary database. However, because of a weid timing scenario that we have, we are wondering if there is a downside to taking a backup of the HADR STANDBY...

hello experts
i am trying to run fixpack 8.3.1.4 over the currently installed 8.3.0.6 on windows server 2003
the problem is that the installation fails because it detects that there was some previous uninstall attempt of the 8.3.0.6 failed and...

How do you run a stored procedure in DB2 Data Studio

In Microsoft SQL Server

I would have written

exec <stored proc name>  'Data', 'Data1'

How do you that in DB2 Data Studio

Note where I have an error on OPEN Cursor1; below
******************************************
CREATE PROCEDURE TestReport ()
      RESULT SETS 1
      LANGUAGE SQL

P1: BEGIN
      -- Declare cursor
      DECLARE cursor1 CURSOR WITH RETURN FOR
      --...

I have the following query

"SELECT * FROM HEN_AUTHOR A JOIN HEN_WROTE W"
             + " ON A.AUTHOR_NUM = W.AUTHOR_NUM "
             + "JOIN HEN_BOOK1 B ON W.BOOK_CODE = B.BOOK_CODE WHERE B.TITLE = ?";

I had created an index on the...

Experts,

I have an existing trigger where a varible of vDATE contians a proper DB2 Timestamp. I later insert this variable into a proper timestamp column.

I want to take the same variable an concatonate it to the end of a text string....

Experts,
I have a rather large select statement in DB2 9.1 (Not a procedure).

I want to add a small subquery that looks for the presence of a value in another table, if found I want to output 'YES', if not I want to output 'NO'.

In SQL,...

Hi,

 We have a Crystal Report. The data source for this report is a DB2 Stored Procedure.
  This procedure has two arguments, one of which is a "Date".

 When i did a verify database, it is throwing a weird error, saying that the date field...

I have a table in AS400, i want to select duplicated fields only, ex bellow is the table
table:bgpf
duplicated fields: BGINID; BGIQID
fileds i want to display:BGINID; BGIQID, BGCUS.
ex:
BGINID      BGIQID                BGCUS
1             ...

Hi,

 We migrated from DB2 UDB 8.2 to DB2 UDB 9.7 recently.
  We still have the Development Center client tool on our local machines. Everything works fine using this tool, but the debugging is not working any more. Is there any setting that...

One of my developers has asked if the following can be done.

-He needs to PULL data from a SQL 2005 table INTO our As400. The SQL 2005 DB server currently has the iSeries v5.3 OLE DB tools installed. How can this been done as simply as possible