DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi Experts,

I try to check a value in a SQL where clause, but get the error: CPD4374

On the  Where xxxx in() of the next statement

Exec SQL Select * from my file
Where Field1 = :Parm1
  and Field2 = in(:Parm2)

Field2 is numeric(15,0)
Parm2 is Character(128) and contains  '12,13,45,16,32  '

What is missing?
0
The Orion Papers
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Environment:
IIS:8.0
.net4.5
windows server 2012
11.PNG 22.PNG
My website sometime will show this error in the web server .

A process serving application pool 'XXX' suffered a fatal communication error with the Windows Process Activation Service. The process id was '6052'. The data field contains the error number.

please help.
0
Thanks for reading.

I have an SQL database with a linked DB2 server. My client software interfaces with SQL.

The DB2 server contains a parts table, which contains pricing and availability information.

I read a linked view from the DB2 server which is presented as a SQL view at the SQL end. It takes a few seconds to come over in its entirety, which isn't a problem unless I want to run some heavy interrogation on it. Which, I do.

Realistically this information shouldn't be more than 10 minutes behind. So, I thought a trigger might be the answer. I have a table I update every 5 minutes for a completely separate reason. I thought I could attach a trigger to this table to drop/insert a temp parts table, and then merge the information to a locally held table.

I've not got as far as the merge yet, just the first bit! Here is the trigger:

USE MyDB
GO

CREATE TRIGGER dbo.UpdateParts
ON dbo.T1
AFTER UPDATE  
  
AS

IF OBJECT_ID('dbo.TempParts', 'U') IS NOT NULL
DROP TABLE TempParts

SELECT     DB2PartsView.*
INTO            TempParts
FROM         DB2PartsView

GO

Open in new window


I can create the trigger fine, and if I run the SQL outside of the trigger the table is created successfully.

However, when I fire the trigger, I get the error:

"The row value(s) updated or deleted either do not make the row unique or they alter multiple rows"

I understand that triggers have hidden 'Inserted' and 'Deleted' tables, and that perhaps this might be something to do with it.

Is a trigger the right thing to use?

Thanks!

John.
0
I have the need to get a BOM with all components , currently using Tc. CK86 , but this doesn't give enough information, so I want to do this by SQL, I come from oracle background and dont know how could I get this done in DB2 R/3, I do not have access to query builder or quickview , but i do have read access via SQL, I'm currently trying to figure out a way of getting this info using tables :

MAST Material to BOM Link
STKO BOM Header
STPO BOM item

Does any of you  have a solution for this?

In Oracle I did something like the following:
SELECT DISTINCT LEVEL
	,sys_connect_by_path(msil.segment1, ' @ ') AS "BOM TREE"
	,msi.segment1
	,lpad(' ', LEVEL, '') || msil.segment1 Cod_Component
	,msil.item_type
	,msil.description Desc_Component
	,BIC.component_quantity
	,msiL.primary_unit_of_measure
FROM mtl_system_items msi
	,bom_bill_of_materials bom
	,BOM_INVENTORY_COMPONENTS BIC
	,MTL_SYSTEM_ITEMS MSIL
WHERE msi.organization_id = 332
	AND BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
	AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_id
	AND bom.bill_sequence_id = bic.bill_sequence_id
	AND nvl(bic.disable_date, sysdate) >= SYSDATE
	AND BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID
	AND Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID
	AND msil.inventory_item_status_code = 'Active'
	AND msi.inventory_item_status_code = 'Active' connect BY prior bic.component_item_id = bom.assembly_item_id
	START
                  WITH msi.segment1 = trim(:parte)
        ORDER BY 2

Open in new window

0
Hi All,

I could be going about this completely wrong; however, I'm working with a field that is originally a number; however, I need to use the Like command in a where clause to isolate certain results.  To further compound this, I need to use the left command to have the where clause only look at the first 5 characters.  

Here is what I have so far:

CAST(MBRLOG.MLCHDT AS VARCHAR(7)) LIKE(LEFT(Date_Table.EligMstrConvert, 5))

Open in new window


I don't always use sql with db2 so I might be slightly off on my logic, but any help or suggestions here would be greatly appreciated.

-Anthony
0
I need to combine address fields(addr_1, addr_2, City, St_cd, zip) into new Mail_Address field where most of the time the addr_2 contains null using DB2

In sql server I can do
rtrim(ltrim(a.addr_1)) + ' ' + rtrim(ltrim(isnull(a.addr_2,''))) + ', '  + rtrim(ltrim(a.City)) + ', ' + rtrim(ltrim(a. St_cd)) + ', ' + rtrim(ltrim(a.zip))
but unable to use in DB2.

I've tried a.addr_1 || '' '' || a.addr_2 || '', '' ||  a.City || '', '' ||  a.St_cd  || '', '' || a.zip  which only return a value in the new field if all fields are not null

I've also tried
Coalesce(a.addr_1,'') || '' '' || Coalesce(a.addr_2,'') . This only returned what was in a.addr_1 even if a.addr_2 wasn't null and I couldn't add the city, st_cd or zip to the coalesce
0
Greetings!

As I make my transition from working on "DB2 for I" to "DB2 for z/OS", I'm finding subtle differences that are tripping me up.

For example, I copied the following simple stored procedure from a website:

CREATE PROCEDURE MYSCHEMA.UPDATE_SALARY_1 (
  IN EMPLOYEE_NUMBER CHAR(10),
  IN RATE NUMERIC(6,2))
LANGUAGE SQL
MODIFIES SQL DATA
  UPDATE MYSCHEMA.EMP
     SET SALARY = SALARY * RATE
   WHERE EMPNO = EMPLOYEE_NUMBER

Open in new window


But, when creating the procedure, it throws the following error:

WLM ENVIRONMENT NAME MUST BE SPECIFIED

I googled that error, and it was recommended that I add "DISABLE DEBUG MODE":

CREATE PROCEDURE MYSCHEMA.UPDATE_SALARY_1 (
  IN EMPLOYEE_NUMBER CHAR(10),
  IN RATE NUMERIC(6,2))
LANGUAGE SQL
MODIFIES SQL DATA
DISABLE DEBUG MODE
  UPDATE MYSCHEMA.EMP
     SET SALARY = SALARY * RATE
   WHERE EMPNO = EMPLOYEE_NUMBER

Open in new window


But, it now throws these errors:

REBIND AUTHORIZATION ERROR USING MYSCHEMA AUTHORITY PACKAGE =
LOCDBT1.MYSCHEMA.UPDATE_SALARY_1.(V1) PRIVILEGE = BIND.      

What am I doing wrong?

Thanks for your help!
DaveSlash
0
How do I find out to see if there is any open transaction in DB2 database ? in sql server we used to use DBCC OPENTRAN. I am using toad for db2

Many Thanks
0
Greetings, experts!

After almost 25 years of working primarily on DB2 for the midrange (AS/400, IBM i), I now have a new job where I need to quickly come up to speed on DB2 for the mainframe (z/OS). Fortunately, the system catalogs in the SYSIBM schema look to be almost the same, so I’ve been able to glean some information about the database-layout from there. But, I keep thinking, “There HAS TO be a better way!”

For example, on DB2 for i, there’s a fat-client GUI application (called “System i Navigator”) where I can easily view and modify table-structure (including column-definition and indexes) and even run SQL queries. I’ve asked around at the new job, but nobody seems to know of a similar application for DB2 on z/OS.

Can anyone point me in the right direction?

Thanks!
DaveSlash
0
I have access database that runs 3 business rules and updates a final table in access.  The logic is in the module rule mod.   I need to loop through 1 million row tables in access and perform procedure logic against the record set.  I am thinking before the database gets to big I should process this information on Teradata platform.  Somehow convert this access module in a stored procedure if that is possible. Or there might be another way through subqueries to get the final outcome.

Sample Database
Customer      Product      Orderdate
11111      VIP      01/01/2017
11111      PLANE      01/16/2017
11111      HOTEL      02/05/2017
22222      VIP      01/01/2017
22222      PLANE      02/28/2017
33333      VIP      01/01/2017
33333      VIP      01/06/2017
33333      HOTEL      01/17/2017
44444      VIP      01/01/2017
55555      VIP      01/01/2017
55555      VIP      02/15/2017
66666      VIP      01/01/2015
66666      VIP      01/28/2017
66666      PLANE      01/29/2017
66666      OTHER      02/01/2017


Business Rule 1 - The first product that has to be ordered is the VIP PRODUCT by customer.
If the VIP PRODUCT is not ordered all rules fail.
Business Rule 2 - If VIP PRODUCT was ordered where there any orders placed within 30 days of the first order for each customer.

Final Output - should look like this:
CustomerID      CustomerName      OrderRuleTest      ProductRuleTest      OrderDateTest      OrderDateFlag
11111      Customer A      PASS   …
0
Visualize your virtual and backup environments
LVL 1
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Experts -

HI,
Using a standard SQL query editor ( like TOAD) , how can I count the total number of tables in a DB2 Database, and also return the count of rows in each table.  

The Database is connected via ODBC connection called an DB2ODBC, the Database name is DB2DB.

Thanks
0
Hi,

  How can i disconnect sessions ( idle or active ) from a DB2 database after some period of time.

  I would like to know how to do it for idle and for active connections.

  The problem is that for some reason the application is not disconnecting the sessions from the database and i would like to be able to disconnect sessions after some period of time.     I will like to find out a way to do it for idle and for active sessions.  Like two different ways for each type of sessions state.

  What i am doing now to solve this problem is rebooting the computer to release all those sessions connections.

 Regards,

   Joe Echavarria
0
Hi,
  We are on DB2 9.7 Linux OS.
We have a Stored Procedure SP_CLIENT_PROGRAM which calls a lot of other Stored Procedures and has a number of tables involved in Select and Inserts.

We have a user group "MDUSER" to which we granted execute on the Stored procedure "SP_CLIENT_PROGRAM".
But that didn't do any good to the users under that group, as they apparently needed access on those individual SPs and tables inside SP_CLIENT_PROGRAM.

Is there a way to grant execute on the SP without granting the objects under it and the user be able to execute it successfully?
0
I need to pull out only the alpha characters from an ID file not numeric values, but this returns the numeric values as well

Please assist have found the following for DB2
SELECT CIN, NATIONAL_ID,FNAME,MNAME,LNAME1,LNAME2
FROM    Prodmgr.T_Consumer
WHERE   CIN  NOT IN  (SELECT CIN FROM Prodmgr.T_REPORT_CONSUMER)
and NATIONAL_ID not like '%?%?'
and ( National_ID not in '%A%'or National_ID LIKE '%B%'or National_ID LIKE '%C%'
or National_ID LIKE '%D%'or National_ID LIKE '%E%'or National_ID LIKE '%F%'
or National_ID LIKE '%G%'or National_ID LIKE '%H%'or National_ID LIKE '%I%'
or National_ID LIKE '%J%'or National_ID LIKE '%K%'or National_ID LIKE '%L%'
or National_ID LIKE '%M%'or National_ID LIKE '%N%'or National_ID LIKE '%O%'
or National_ID LIKE '%P%'or National_ID LIKE '%R%'or National_ID LIKE '%S%'
or National_ID LIKE '%T%'or National_ID LIKE '%U%'or National_ID LIKE '%V%'
or National_ID LIKE '%w%'or National_ID LIKE '%X%'or National_ID LIKE '%Y%'
or National_ID LIKE '%Z%')
ORDER by National_ID DESC;
0
Is there a difference bwteen selecting rows in the WHERE clause on in the JOIN clause.

For example,

SELECT t2.*
  FROM orderHdr t1
  JOIN orderDtl  t2 on t2.orderno = t1.orderno
  WHERE t1.orderno = 100
       AND t2.status = ' '

as opposed to

SELECT t2.*
  FROM orderHdr t1
  JOIN orderDtl  t2 on t2.orderno = t1.orderno  AND t2.status = ' '
  WHERE t1.orderno = 100

I would think the second method would be faster as I'm thining that join is only to rows where the status is blank.

Thanks for your thoughts,

Rob
0
I'm creating a simple Access frontend for a DB2 table. Using the following code updates the contents of the pass through query correctly but fails to execute the query. What do I need to change to get the query to execute?

Private Sub cmdAdd_Click()
Dim ssql As String, edNum As String, edNam As String
Dim qd As DAO.QueryDef, db As DAO.Database

Set db = CurrentDb()
Set qd = db.QueryDefs("CmpInsert")

If IsNull(Me.edtNum.Value) Then
    MsgBox "Please enter a Competitor Number", vbOKOnly
    Me.edtNum.SetFocus
    Exit Sub
Else
    TempVars!edNum = Me.edtNum.Value
End If

If IsNull(Me.edtName.Value) Then
    MsgBox "Please enter a Competitor Name", vbOKOnly
    Me.edtName.SetFocus
    Exit Sub
Else
    TempVars!edNam = Me.edtName.Value
End If

ssql = " insert into pwrdta.zzccompp (zzccomn, zzcdesc)" & _
    " values ('" & TempVars!edNum & "',  '" & TempVars!edNam & "') "

qd.SQL = ssql

Me.Refresh
End Sub

Open in new window

0
Hi All,

     We are on DB2 Linux 9.7 database version.
I have a Stored Procedure which returns a result set. I need to store that result set into a table.
How can i do it?
Please advise.
0
I have installed Client Access (7.1) on our MS SQL 2014 server.  I am using the IBMDA400 OLE driver to create a linked server to DB2 database one the iSeries. When I run a select statement on the data in a table a couple of fields are legible but most did not translate.

The fields that translate properly are ccsid 37 the ones that did not are ccsid 65535.

I have read several posts and tried putting various translation options in the Provider string.  It seems as soon as I add add any additional string other than the "Default Collection = xxx" in the provider string the connection test fails.

Can anyone steer me in the right direction?
0
Hi All,

   We are on DB2 Linux 9.7 database.
I have a Stored Procedure, which passes the client's first name, last name, DOB and SSN, to retrieve client's information.
In the below query, the variables (V_) passed for the first and the last names may not be exact.
Still we need to get those results back.

SELECT * FROM CLIENT_TABLE
WHERE SSN_NO = V_SSN_NO
AND DOB_DT = V_DOB_DT
AND UPPER(FIRST_NM) = UPPER(V_FIRST_NM)
AND UPPER(LAST_NM) = UPPER(V_LAST_NM)

For example: First name may be Andy. But the client has entered Andie in the parameter. We still need to pull that record.
How can i change the above query to achieve this functionality?
Please advise.
0
Free Tool: SSL Checker
LVL 9
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

This is an ongoing project to sync Office Vision on a 610 (v4r5) to backup system a 170 (v4r4).

We did a full system save of the main system and restored it to the backup system, changed license keys, IP address, and address Id on the backup machine then did a full system save on it.

The restored receivers where not what I expected (please see attached document). I've include screen shots and steps I took. The questions are posted at the bottom of the document. Basically I'm not able to restore the journals (have not tried apyjrnchg with the seq numbers).

Thank you for any assistance.
Lynn Sync-Information.docx
0
i want to check if the Windows server has the following software installed on it.

IBM DB2
IBM IMS
Unisys RDMS
Unisys DMS
Sybase
Oracle

I believe we can check this if there are services running for each Software and need to know what service will indicate that the software is installed?
Any one please.
Thanks
0
Update: I'm trying to sync office vision email changes between to old systems for recovery purposes. Office vision is the only application running both systems:  active system is a 610 v4r5 and the backup system is a 170 v4r4.  

Plan:
Sync users, configuration, etc. once. Then any changes made to the live system (610) will also be made to the backup system (170) manually for these areas. However, documents and email changes by users need to be synced a few times throughout the day. (depending on what the final solution will allow due to availability, speed, etc.) The hope is to sync the folders/files & email every half hour to hour.

 (should be noted I'm a newbie to journaling)

Background:
I would like to use QAOSDIAJRN for email recovery. Documents, as well, if they are recoverable is the same journal. Otherwise SAVRSTDLO will be used to folders/files. The SAVRSTDLO was successful with my own library but I have not done a full test until I get the email working. (I have created a job to grant myself permission to handle email for all current users; if the savrst is required.)

I have not tried remote journaling with redirect because it seems to me the results would be the same (newbie).  Plus,  I'm hoping not to mess with system journals. Whether I use SAVRSTOBJ on the receiver and pass it to the remote system or use remote journaling it seems either way I end up with a receiver on the remote system which must somehow be recognized by the system journal …
0
LPSDT and LPFDT are date and time stored as Packed Decimal. I am attempting to extract the information out and put it in time format and calculate Time Spent.

select LPEM as "Employee", LPLC as "LaborCode", 
to_date(char(lpsdt), 'YYYYMMDDHH24MISS') as "Start Date/Time",
to_date(char(lpfdt), 'YYYYMMDDHH24MISS') as "Finish Date/Time",
to_date(char(lpfdt), 'YYYYMMDDHH24MISS') - to_date(char(lpsdt), 'YYYYMMDDHH24MISS') as "Time Spent",
from labor.empmngmnt

Open in new window

0
I have a field (LPSDT) that is a date and time in a string format: 20170312112507

In my query I substring the time as substr(LPSDT, 9, 2)||':'||substr(LPSDT, 11, 2)||':'||substr(LPSDT, 13, 2) which results in 11:25:07 but it is a string not an actual time format.

If I use the following: time(substr(lpsdt, 9, 6) I get 3/1/2017 11:25:07 AM as a result which makes no sense since I am substringing the data that only equates to the time and the date it is returning is wrong. The date does not match what is actually in the field. The system seems to be making assumptions about the data rather than simply returning the expected value.

I've tried to_date with a format of 'hh24miss' and get the same result.

Any ideas?
0
Hi,
Our shop has not used journal until a couple months ago when I added them to a couple master files for audit trail purpose. So, my experience with journaling is very limited. I don’t mind digging but would like to not chase my tail for hours on something that is not possible if I can help it.

Can I add remote journaling to a system journal? Specially, QAOSDIAJRN (Journal for DIA files). I have been researching how to setup a remote journal and thought I read somewhere remote journals could not be done on ‘Q’ files outside QGPL. Has anyone done a remote journaling on this system journal before? Any tips?

Thank you in advance!
Lynn
0

DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.