Articles & Content

StackOverflow

StackOverflow is a collaborative programming Q&A community venture

Note: The content of this blog is the opinion and thoughts of the blogger and does not necessarily represent the opinions of IDUG.

 

 
DB2 decimal fields come back as negative DBTYPE_NUMERIC from OLEDB

Using OLEDB from C++ I get DBTYPE_NUMERIC from DB2 for decimal fields and convert to DECIMAL like so (error checking omitted):

...CDynamicAccessor* pAccessor = GetAccessor();...DBTYPE dbT=0;pAccessor->GetColumnType(nIndex, &dbT);if (dbT == DBTYPE_NUMERIC){ DB_NUMERIC* pNum = (DB_NUMERIC*)pAccessor->GetValue(nIndex); DECIMAL dec; DECIMAL_SETZERO(dec); dec.sign = pNum->sign; // <--this comes back as 1 for positive fields and 0 for negative dec.scale = pNum->scale; memcpy(&dec.Hi32, pNum->val + 8, 4); memcpy(&dec.Lo64, pNum->val, 8); ....}

What I don't understand is why DB_NUMERIC::sign coming back as 1 for positive decimal fields in DB2 and 0 for negative. Is this correct behavior? By definition here it's the opposite for DECIMAL - 0 should be for positive.Is it a db/field setting? In database everything looks positive. And if I set a value in db as negative it comes back positive. So the sign is turned on in the returning DB_NUMERIC structure for positives and turned off for negative. It's a reverse/inverse of what seems to be expected. What's wrong?BTW: I can't find any docs on DB_NUMERIC that is in oledb.h anywhere.Please help. Thanks a lot in advance!

How to check all user abends with single if condition in JCL

How to check all user abend codes using single if condition in JCL?

Explanation: My job is abending with different user abends

Reference.

Currently I am checking like this

// IF (P535S040.RC > 0) THEN 

It is checking for only return code, but I need to check user abends also.

Why does my query generate an "undefined name" error with SQLCODE -204?

This is a SQL Server query

select Letter, COUNT(*)from #LettersLEFT JOIN Emp ON Name LIKE Letter + '%'group by Letter

but it would not run in DB2, giving an error message:

"MOHIT.#LETTERS" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.18.60

How can I inserting a string with a single quote in it into a DB2 table?

I'm using a DB2 database.

How to insert ' (single quote) as data into a table.

For example:

Insert into EMP values ('Raju's kumar' , 10000)

where the name has a ' (single quote).

DB2 cannot delete or update rows after insert

i am facing the following issue:

I have some n:m entities in my spring application with DB2 behind. Lets say they are mapped via the tables APPLE, WORM and APPLE_WORMS.

I ran a generic SQL script to insert some worms, like that:

INSERT INTO WORMS (ID, VERSION, NAME, TOXIC) VALUES ((SELECT MAX(ID)+1 FROM WORMS),0,'Hans','very');INSERT INTO APPLE_WORMS (APPLE_ID, WORM_ID) VALUES ((SELECT ID FROM APPLE WHERE APPLE_NAME = 'Granny'),(SELECT MAX(ID) FROM WORMS));

Back to the application everything seems to be at its place. All the apples got their worms, but:

Now when i try to update or delete those DB entries, the queries will run forever and never succeed.

I am far from being an expert, even worse i might be dumb to ask, but why is it like this?

reproduction on other environment:

DELETE FROM WORMS WHERE ID IN (-- ids of worms to be delted );-- assosiated entries in APPLE_WORMS are deleted automaticlyINSERT INTO WORMS (ID, VERSION, NAME, TOXIC) VALUES ((SELECT MAX(ID)+1 FROM WORMS),0,'Hans','very');INSERT INTO APPLE_WORMS (APPLE_ID, WORM_ID) VALUES ((SELECT ID FROM APPLES WHERE APPLE_NAME = 'Granny'),(SELECT MAX(ID) FROM WORMS));

i can rerun this, i can update those entries.

So i think, the reason why i cannot change them on the other environment might be that the application blocks those entries from being accessed?

Export CSV from Mainframe DB2 in batch mode

how can I export in a CSV file the result of a SELECT query from Mainframe DB2 in Batch mode?I have tried the FILE MANAGER online mode and it works but I need to use the batch mode for a better performance. I can also use ISQL but I don't know which parameters I have to use to create a CSV file.Thanks

Having trouble writing output of a rexx program running from a batch file to an output file
for /f "tokens=1-4 delims=/-\ " %%a in ('date /t') do ( SET mjour=%%a SET mmois=%%b SET mannee=%%c)SET date=%mjour%%mmois%%mannee%echo 'hello' > "D:\db2support\dbauditarchive%date%.log" 2>&1D:\DB2_V9\IBM\SQLLIB\BIN\db2cmd.exe "rexx D:\db2support\dbauditarchive.cmd" > D:\db2support\dbauditarchive%date%.log 2>&1

I am running a REXX program containing DB2 commands. To run this REXX program I created the .bat file as shown above. But my output never gets written to the output(log) file. The file gets created but is blank. The REXX file contains no errors and if I run it through a cmd window the the output shows on the cmd window. Can you see what is my mistake here?

DataStage DB2 Runtime Column Propagation with Unicode Data

I'm trying to read some data from DB2 9.7 using DataStage 9.1 using the Runtime Column Propagation (RCP) feature. The general approach is to set the DB2 Connector Stage to generate the query SQL by specifying only the connection details and table name. This approach works in most cases but I'm running into a problem with multibyte characters.

When I have this setup and the data being read is from one of the CJK (Chinese, Japanese, or Korean) languages, I see ^Z characters in the sequential file that I write to. When I write this data to a Data Set I can see that the schema incorrectly specifies that the column's data type is string instead of ustring. From reading the default type conversion documentation it appears that DataStage is reading the data from DB2 as a ustring but is then trying to put it into the generated dataset as a string.

When I generate a Table Definition for the table I can see that it's generated the columns with the string type. However, when I load the table definition into a DB2 connector I see a checkbox, which is checked by default, that will automatically add the Unicode extended attribute to character columns.

With all of the above information it seems like DataStage is capable of generating ustring columns instead of string, but it doesn't do it for runtime column propagation.

In short: is there any way to convince DataStage to generate ustring columns when using RCP? Is there a setting on the DB2 Connector stage? Is there a setting at the project level? Is there an environment variable that controls this? If not, do I then need to build table definitions for every table and write a custom job to extract that data solely because of the problem I'm seeing?

DB2 python insert list of dictionaries

I have a list of dictionaries that I'd like to insert into a DB2 table. How can I do that? I'm using the ibm_db DB2 driver. Here's my code:

list_of_dictionaries = []sql = "INSERT INTO schema.table VALUES (...)"ibm_db.exec_immediate(conn, sql)

The problem is that the table as well as the dictionaries have more than a hundred columns/keys, so I prefer not to list all column names in my SQL query.

IBM.Data.DB2.iSeries SQL0180 error

I'm attempting to connect to IBM.Data.DB2.iSeries database via a .NET application and am receiving the following error when executing a DB2 stored procedure: "SQL0180 Syntax of date, time, or timestamp value not valid."

However, when i run the same stored procedure in the IBM Data Studio i get no error and it successfully returns a from the execution of the stored procedure. This leads me to believe it may be a driver issue and i'll need to update it. Has anyone ran into this issue before and was it a driver update that fixed it?