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.

 

 
How do I get ibm_db or PyDB2 python modules to work with DB2 in Mac OS X 10.7 Lion?

I used this question/answer to install DB2 in Lion: How do I install IBM DB2 Express-C on Mac OS X 10.7 Lion?

After configuring my databases, I am able to use db2 from the command line to execute queries, but the python modules ibm_db and PyDB2 both fail to import with the following error:

>>> import ibm_dbTraceback (most recent call last): File "", line 1, in ImportError: dlopen(/Library/Python/2.7/site-packages/ibm_db-1.0.4-py2.7-macosx-10.7-intel.egg/ibm_db.so, 2): Symbol not found: _dsIsDirServiceRunning Referenced from: /Users//sqllib/lib64/libdb2.dylib Expected in: /System/Library/Frameworks/DirectoryService.framework/Versions/A/DirectoryService

How can I fix this and get ibm_db and PyDB2 to work properly with DB2 in Lion?

edit: moved answer to answers

How to get only one record for the minimum wage employee from each department? DB2

I barely just started on database languages, so I do not know anything about JOIN, PARTITION, ETC. but this is what I have so far.

What I tried:

SELECT MIN(SALARY) AS "MIN SALARY", WORKING.DID, ENAME FROM DEPARTMENT, EMPLOYEE, WORKING WHERE WORKING.EID = EMPLOYEE.EID GROUP BY WORKING.DID, ENAME;
Result: MIN SALARY DID ENAME ---------- --- ------ 10000 101 Dustin 15000 102 Bob 20000 102 David 10000 102 Dustin 10000 103 Alex 8000 103 Alice 7000 103 Mike

What I want:

 MIN SALARY DID ENAME ---------- --- ------ 10000 101 Dustin 10000 102 Dustin 7000 103 Mike

Table structures:

CREATE TABLE EMPLOYEE ( EID INTEGER NOT NULL, ENAME VARCHAR(25), SALARY DECIMAL, PRIMARY KEY (EID));CREATE TABLE WORKING ( EID INTEGER NOT NULL, DID INTEGER NOT NULL, STIME DATE, FOREIGN KEY (EID) REFERENCES EMPLOYEE (EID), FOREIGN KEY (DID) REFERENCES DEPARTMENT (DID), PRIMARY KEY (EID, DID));CREATE TABLE DEPARTMENT ( DID INTEGER NOT NULL, DNAME VARCHAR(10), DADDRESS VARCHAR(20), PRIMARY KEY (DID));

ER Diagram

DB2 creating a random but unique character identifier upon row insert

I currently have a column in a DB2 table which is being passed through web calls and procedure by a character-encrypted value. It is type CHARACTER(13) with a CSSID for encryption.

This has become a huge pain to accommodate through multiple APIs but was initially intended to allow us a unique ID to use in calls that wasn't the primary key.

In DB2-400, what would be the next best thing as far as a 13 or more character string that is unique and randomly created upon insert, but doesn't require decryption (just a plain string)?

Is there a commonly-gravitated-to method for this? We aren't passing secure data, so there's no need for encryption, but we just want a randomly created and unique character

hex values with CSSID in DB2-400

I have a stored procedure where I"m passing a value (from CHARACTER(13) with CSSID 65535 and trying to return an ID by it

The procedure is:

BEGINDECLARE GET_ID_BY_WEB_IDENTIFIER_C1 CURSOR WITH RETURN FORSELECT ID FROM TABLE . ITEMT WHERE WEB_IDENTIFIER = P_WEB_IDENTIFIER ;OPEN GET_ID_BY_WEB_IDENTIFIER_C1 ;END 

and the parameter P_WEB_IDENTIFIER Is a CHARACTER(26) with a matching cssid. I've also tried this with it being a character 13 with same results.

When I call this:

CALL PROGRAM . GET_ID_BY_WEB_IDENTIFIER (x'0213725501A421B9A457123001')

I get back my ID, but when I call like this (as it is called in production through the web):

 CALL PROGRAM . GET_ID_BY_WEB_IDENTIFIER ('0213725501A421B9A457123001')

I get a conversion error

How can I make sure that passing only the string '0213725501A421B9A457123001' will return my ID this way?

Adding parameters to SQL select and using them in Cognos Report Studio Version 10.2.1

i'm trying to put into my select a date parameter. I'm not sure how really to do this. Right now I have fixed date value in it and now I want to have a parameter like: FromDate and ToDate.I'll be really grateful for any help.My select:

select laborcode,(select sum(workhours) from workperiod where calnum='KOPA'and workdate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY')) - (select count(calnum) from workperiod where calnum='KOPA'and workdate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY') and shiftnum='HOLIDAY') * 8 as stevilo_praznikov_ure,(select sum(lt.regularhrs) from labtrans lt where lt.laborcode = l.laborcode and lt.orgid = l.orgid and startdate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY')) as delovne_ure,(select sum(ass.laborhrs) from assignment ass where ass.laborcode = l.laborcode and ass.scheduledate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY')) as assignment_urefrom labor l
Connect and retrieve result set from DB2 Stored Procedure from Node js

I am able to find solution to connect db2 tables directly and fetch data online.Please find working code for connecting db2 Stored Procedure from Nodejs with IN and OUT parameter.

Kindly let me know if anyone have a better approach for the same.

const ibmdb = require("ibm_db");const connStr = 'DRIVER={DB2};DATABASE=;HOSTNAME=;UID=;PWD=;PORT=;PROTOCOL=';var query = "call SCHEMA.SP_NAME(?, ?, ?, ?,?, ?, ?, ?)";var result;ibmdb.debug(true);ibmdb.open(connStr, function (err, conn) { if (err) { console.log(err); } param1 = { ParamType: "INPUT", DataType: 1, Data: "R2" }; param2 = { ParamType: "INPUT", DataType: 1, Data: "" }; param3 = { ParamType: "INPUT", DataType: 1, Data: "" }; param4 = { ParamType: "INPUT", DataType: 1, Data: "000000250" }; param5 = { ParamType: "OUTPUT", Data: "" }; param6 = { ParamType: "OUTPUT", DataType: 1, Data: "", Length: 1 }; param7 = { ParamType: "OUTPUT", Data: "" }; param8 = { ParamType: "OUTPUT", DataType: 1, Data: "", Length: 5 }; //Call SP Asynchronously. conn.prepare(query, function (err, stmt) { if (err) console.log(err); stmt.execute([param1, param2, param3, param4, param5, param6, param7, param8], function (err, result, outparams) { if (err) console.log(err); else { data = result.fetchAllSync(); console.log("\nResult for Async call of PROC ==>"); console.log("\tOut Params = ", outparams); console.log("\tResult Set = ", data); while (result.moreResultsSync()) { data = result.fetchAllSync(); console.log(data); } result.closeSync(); } stmt.closeSync(); conn.close(function (err) { console.log("done."); }); }); });});
sql query to get last 6 months of data

I am trying to get the data for the last 6 months.

This is what I have used:

WHERE d_date > DATEADD(m, -6, current_timestamp)

and I am getting this error.

ERROR: CLI prepare error: SQL0206N "M" is not valid in the context where it is used

also tried

WHERE d_date > current date -180 

and got this error:

ERROR: CLI prepare error: SQL0171N The data type, length or value of the argument for the parameter in position "2" of routine "-" is incorrect. Parameter name: "". SQLSTATE=42815

Please advice.

How to select a substring surrounded by delimiters in DB2 SQL?

I would like to select the substring between '/' delimiters.This is my sample data:

1/1/199013/111/1990131/1/19902/11/1990

I want to get this output:

1 1 199013 111 1990131 1 19902 11 1990

I have tried this:

SELECT date,SUBSTRING(date, LOCATE('/', date)+ 1, LOCATE('/', date)-1) FROM data

Unfortunately, when the date is 13/1/1990 or 1/13/1990, it doesn't work.

How to avoid CTQ operator in DB2 Blu with substr?

I'm working with DB2 Blu, and I often have problems in my queries plan with SUBSTR as the CTQ operator is not high enough.

Here is an example :

selectcoalesce(annee,annee_semaine),count(JOUR_OUVRE)from d_tps_calendriergroup by grouping sets((annee),(annee_semaine))

The query plan is all right :

Rows RETURN ( 1) Cost I/O | 2701 L TQ ( 2) 334.257 224 | 2701 C TQ ( 3) 333.573 224 | 2701 UNION ( 4) 332.721 224 /-+--\ 2649 52 GRPBY GRPBY ( 5) ( 10) 166.197 166.107 112 112 | | 22310 22310 TBSCAN TBSCAN ( 6) ( 11) 165.176 165.176 112 112 | | 22310 22310 TEMP TEMP ( 7) ( 7) 147.121 147.121 112 112 | 22310 GRPBY ( 8) 134.404 112 | 22310 TBSCAN ( 9) 132.702 112 | 22310 CO-TABLE: INFIFZ00 D_TPS_CALENDRIER_INF Q1

Now, what I really want to do is a group by the first 3 characters of annee_semaine :

selectcoalesce(annee,substr(annee_semaine,1,3)),count(JOUR_OUVRE)from d_tps_calendriergroup by grouping sets((annee),(substr(annee_semaine,1,3)))

And now in the query plan, the group by and union are done after the CTQ operator

Rows RETURN ( 1) Cost I/O | 2701 UNION ( 2) 431.235 224 /-+--\ 2649 52 GRPBY GRPBY ( 3) ( 14) 220.453 210.366 112 112 | | 2649 22310 TBSCAN TBSCAN ( 4) ( 15) 220.219 208.372 112 112 | | 2649 22310 SORT TEMP ( 5) ( 7) 220.219 190.317 112 112 | 22310 TBSCAN ( 6) 208.372 112 | 22310 TEMP ( 7) 190.317 112 | 22310 LM TQ ( 8) 181.039 112 | 22310 GRPBY ( 9) 165.712 112 | 22310 TBSCAN ( 10) 163.749 112 | 22310 SORT ( 11) 161.817 112 | 22310 C TQ ( 12) 132.866 112 | 22310 TBSCAN ( 13) 132.702 112 | 22310 CO-TABLE: INFIFZ00 D_TPS_CALENDRIER_INF Q1

I tried with left instead of substr, without success. Is there a good pratice to deal with substr in DB2 Blu ?

In others cases, I was able to directly put de substr in one column in the table, mais in this case it is not possible.

DB2 extract data between two delimiters

Here is the string I am trying to extract from:'cn=xyxyxyxyxyx ousy,ou=information services,ou=domain users,dc=corp,dc=xyxyxx,dc=com'

I am trying to extract the string between the first 'ou=' and the second comma. In this case that is'information services'

Here is what I have so far:SUBSTR(F_DN, locate('ou=', F_DN)+3, locate(',', F_DN, locate(',', F_DN)+1)+1 ) as roleAnd this is the result:'information services,ou=domain users,dc=co'

It seems to locate to the first character just fine but I cannot get the length correct.