DB2

5K

Solutions

3

Articles & Videos

3K

Contributors

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 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
On Demand Webinar: Networking for the Cloud Era
LVL 8
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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
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
[Webinar] Learn How Hackers Steal Your Credentials
LVL 8
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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
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
DevOps Toolchain Recommendations
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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
I'm asking this for a co-worker.  He has been tasked with migrating data from an IBM AS/400 iSeries DB2 database to a MS SQL 2016 environment.  He tried to use the Microsoft SQL Server Migration Assistant tool but apparently it doesn't support connection to IBM iSeries.

He wants to know if anyone knows if Microsoft will be adding support for IBM AS/400 iSeries to the SQL Server Migration Tool.  Or if there are other tools out there that does the same thing (allows for easy migration of database from IBM iSeries DB2 to MS SQL).

Thanks!
0
1
DB2 is installed successfully without errors, but when we opened db2.exe it is just flashing and disappears and also when we opened db2cmd.exe it is not taking the installation path as it is going to windows/system32 path.(Installation path is E:/IBM/SQLLIB).Kindly suggest that we have installed the db2 correctly or need to re-install?
0
Hi,
I have two tables join as below giving  results as below:

query:

select a.emplid,a.name,b.erncd
from ps_check a
left outer join ps_earning b
on a.paygroup=b.paygroup
a.line_num =b.line_num
a.page_num =b.page_num
where  b.erncd='AA'

Current results :

EMPLID NAME  ERNCD
101           sam    AA
101          sam    
102         dave  
103        charles  AA
103      charles  


Results should only display where emplid is not equal to AA as below:

EMPLID NAME  ERNCD
102        dave  

Note i don't want to do minus between two sql queries.
0

DB2

5K

Solutions

3

Articles & Videos

3K

Contributors

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.

Top Experts In
DB2
<
Monthly
>