Use this tag for questions about DB2 for z/OS, the IBM mainframe operating system. Please also tag such questions with the more generic [db2] tag. There are enough differences between DB2 for z/OS and DB2 for other platforms to warrant a separate tag.
Questions tagged [db2-zos]
175 questions
0
votes
1 answer
How to extract date from a string column in db2 anything equivalent to patindex
I have a column in a table as history comment where associate manually enters the text along with date in d/mm/yyyy format.
I want to extract the date and store it another column
I did in MSSSQL using substring and patindex
0
votes
0 answers
Why are multiple numbers not workin in IN clause for DB2 z/OS
So I have the following query:
SELECT * FROM SOMESCHEMA.SOMETABLE
WHERE ID IN (123);
Which does work as intended.
But
SELECT * FROM SOMESCHEMA.SOMETABLE
WHERE ID IN (123,456);
Does not work!
But "funny":
SELECT * FROM SOMESCHEMA.SOMETABLE
WHERE ID…
Pwnstar
- 2,333
- 2
- 29
- 52
0
votes
2 answers
Db2 Multi Insert using LOOP
I am trying to write a loop basically to do bulk INSERTS for DB2 Z/OS.
After referring this article, I tried to use this:
BEGIN
DECLARE varcnt;
set varcnt int DEFAULT 0;
WHILE varcnt <= 10 DO
insert into HEROI.TESTD (PK,XML_TYPE)…
arsm4
- 101
- 2
- 10
0
votes
1 answer
SQL Db2 - How to unify two rows in one using datetime
I've got a table where we have registries of employees and where they have worked. In each row, we have the employee's starting date on that place. It's something like this:
Employee ID
Name
Branch
Start Date
1
John…
Moisés Almeida
- 31
- 3
0
votes
1 answer
How to create new SQL instance in groovy using secure DB2 z/OS connection?
I am groovy beginner with minimal java knowledge. Trying to access DB2 z/OS from groovy script.
With not secure connection below sample works fine for me:
import java.sql.*;
import groovy.sql.Sql
// DB2
sql = Sql.newInstance(…
Leonid Tepliakov
- 31
- 4
0
votes
2 answers
-567 Rebind Authorization error for Auth ID IBM Db2 native stored procedure
A user is unable to create a native stored procedure via IBM data studio and faces -567 rebind authorization error for a particular auth id say DBAXXYY.The schema name is also same as the authid
But I am able to create the stored procedure…
Sudarsan Rajagopalan
- 83
- 8
0
votes
0 answers
Syncsort produces a non-readable output for decimal(9,8) or smallint data type columns in Db2
I am using Syncsort to select records from Db2. For the columns that are either decimal(9,8) or smallint the output looks weird with junk characters in it. If I cast the column to CHAR type in the select statement the output is proper. I do not…
Sudarsan Rajagopalan
- 83
- 8
0
votes
2 answers
Db2 v12 z/OS password hashes table
Im trying to locate user password hashes in Db2 v12 z/OS. I think in previous versions this was located in the SYSIBM.USERNAMES table - but not this time. Any clue?
Radek Radek
- 13
- 3
0
votes
1 answer
DB2 for z/OS: CURSOR FOR UPDATE locking behavior
I have a question concerning the FOR UPDATE Clause in CURSORs for IBM DB2 for z/OS.
Assume Isolation Level Cursor Stability (standard parameter in BIND command).
DB2 Version is 11.
My first question is: can a CURSOR that is coded with the FOR UPDATE…
Monty Burns
- 101
- 1
- 3
- 10
0
votes
1 answer
DB2 Z/OS - Revalidate UDF and Stored Procedure
On DB2 AIX I can use the SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS stored procedure to revalidate all Stored Procedures and Functions defined in my schema.
How can I do the same thing on DB2 z/OS (v.12)?
Thanks
killer
- 11
- 6
0
votes
0 answers
Calling Package from Stored Procedure - Db2 z/OS
I´ve been trying to call a Package from a Stored Procedure, but I'm not able to.
I tried to create it as below:
CREATE PROCEDURE DGOWNDB.ZHG0002I
(IN col1 CHAR (03) FOR SBCS DATA CCSID EBCDIC, …
Ricardo Pinto
- 333
- 1
- 2
- 11
0
votes
0 answers
Running multiple statements on DB2 z/OS (v12)
I'm trying to run multiple statements at once. When I run each of the following 3 statements on its own, I get no issues. However, when I run all 3 at once, I get this error. What am I doing wrong?
My server is v12 and I am using DBeaver Community…
Ray
- 5
- 2
0
votes
1 answer
DB2 z/OS: Problem to create UDF to return UNICODE string
I need to create user defined function to return utf-8 UNICODE string.
When I am trying to create it I get the error message:
THE ENCODING SCHEME SPECIFIED FOR THE FUNCTION MUST BE THE SAME AS THE CONTAINING OR TARGET TABLE SPACE OR OTHER…
Leonid Tepliakov
- 31
- 4
0
votes
0 answers
Error SQL30073N "0x2110" Parameter value "0x0000" is not supported. Python DB2 connection
I am trying to connect to a DB2 database using python. For this I am using the ibm_db_dbi package.
The problem is that when I try to make the connection, I get the following error:
ibm_db_dbi.ProgrammingError: ibm_db_dbi::ProgrammingError: [IBM][CLI…
Xabagiar
- 9
- 1
0
votes
0 answers
Cobol call Stored Procedure - SQLCODE -440
I am using DB2 12 (Z/OS).
I have a cobol Db2 program (name: TESTSS)
This program calls an SQL Stored Prodecure (SP_TEST1)
The Stored Procedure is defined on the STXXAA scheme.
During BIND I get the sqlcode -440.
BIND command (the job that performs…
killer
- 11
- 6