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.

 

 
Exclude records in AVG SQL function? (can't use WHERE in main query because of another count)

my SQL query which works fine is

select case month(timestamp_iso(STATUSDATE)) when 1 then 'January' when 2 then 'February' when 3 then 'March' when 4 then 'April' when 5 then 'May' when 6 then 'Jun' when 7 then 'July' when 8 then 'August' when 9 then 'September' when 10 then 'October' when 11 then 'November' when 12 then 'December' end as Month, count (case when service='ADSL' then 1 end) as ADSL, AVG (timestampdiff( 4, char(actualfinish - reportdate))/60.00) as efficiecnyfrom INCIDENTwhere year(STATUSDATE) = year(current_timestamp) group by month(timestamp_iso(STATUSDATE))

I want to get for each month number of services with ADSL (it is done through the first COUNT)and average time difference in hours for the records which do not have service ADSL. So I must exclude in the AVG function all records with service ADSL but I can not put it in where clause

 where year(STATUSDATE) = year(current_timestamp) and service!='ADSL' 

because my COUNT function needs to have service='ADSL'

How to solve this?

Thanks

What is the syntax problem here using this subquery inside where clause
SELECT p.pnum, p.pnameFROM professor p, class cWHERE p.pnum = c.pnum AND c.cnum = CS245 AND (SELECT COUNT(*) FROM (SELECT MAX(m.grade), MAX(m.grade) - MIN(m.grade) AS diff FROM mark m WHERE m.cnum = c.cnum AND m.term = c.term AND m.section = c.section AND diff <= 20)) = 3

Incorrect syntax near ')'. Expecting AS, FOR_PATH, ID, or QUOTED_ID.

DB2 Dynamic query execution which returns row count

Here i am trying to run a dynamic query in DB2 which returns row count in a variable.

Could anyone help me out how to store the result of a dynamic query into a variable so that I could use it further? Below are code snippet:--

CREATE PROCEDURE SP_REC_COUNT ( ) LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION MODIFIES SQL DATA CALLED ON NULL INPUT INHERIT SPECIAL REGISTERSBEGINDECLARE SQL_STR VARCHAR(5000);DECLARE V_TabSchema VARCHAR(50);DECLARE V_TabName VARCHAR(100);DECLARE CNT INTEGER DEFAULT 0;DECLARE CountCur CURSOR FOR select TabSchema,TabName from SysCat.Tables where TabSchema='NGCORE' or TabSchema='NGCOREAU'; OPEN CountCur; CountCur_Loop1:LOOP FETCH CountCur INTO V_TabSchema,V_TabName; IF SQLCODE = 0 THEN SET SQL_STR ='select count(*) into CNT from '|| V_TabSchema||'.'||V_TabName; PREPARE SQ FROM SQL_STR; execute SQ; SET SQL_STR ='INSERT INTO NGCORE_TMP.PHYISICAL_BACKOUT_REC_CNT(SCHEMA_NM,TABLE_NM,BEFORE_CNT)VALUES('''||V_TabSchema||''','''||V_TabName||''','||CNT||')'; PREPARE SQL FROM SQL_STR; EXECUTE SQL; ELSE CLOSE CountCur; LEAVE CountCur_Loop1; END If; END LOOP CountCur_Loop1;END;

Getting below error:- ERROR [42703] [IBM][DB2/AIX64] SQL0206N "CNT" is not valid in the context where it is used. SQLSTATE=42703

How to call User-define function with out schema name in dynamic query in DB2 Procedure

Without schema name its not able to call user defined function.

CREATE PROCEDURE TEST_PROC ( IN IN_EMP_ID INT)LANGUAGE SQLDYNAMIC RESULT SETS 1ISOLATION LEVEL CSDISABLE DEBUG MODEVALIDATE BINDDYNAMICRULES BINDQUALIFIER ABCBEGINDECLARE STMT VARCHAR(500) ; DECLARE emp_curr CURSOR WITH RETURN FOR TRANSACTIONS; SET STMT = ''; SET STMT = STMT ||'SELECT ' || 'EMP_ID, ' || 'ABC.TEST_FUNCTION(EMP_ID), ' || 'EMP_NAME, ' || 'SALARY, ' || 'COMPANY ' || 'FROM ' || 'EMP_DETAILS ' || 'WHERE 1=1 '; IF IN_EMP_ID IS NOT NULL THEN SET STMT = STMT || ' AND EMP_ID =' || IN_EMP_ID ; END IF ; PREPARE TRANSACTIONS FROM STMT ; OPEN emp_curr;END;

when i am trying to execute this procedure its working but if i am remove alias name from my function as highlighted so its unable to call user define function. pls help me out thanks in advance.

Mainframe job dependency in SQL Server job

I have a SQL Server job which has to check if the mainframe DB2 job is complete (for the data) and then run the SSIS packages. I plan to add step 1 to check if the job is complete. I cannot add the job name directly in stored procedure which is one of the ways we write two SQL jobs dependency. Is there any other way?

Unable to connect to DB2 via ODBC with DB2CLI.exe in Windows Docker container

Problem

I have a DB2 instance, and I'm trying to access it via a Windows Docker container using an ODBC connection. I installed the ODBC drivers and DSN using DB2CLI.exe, but when I go to validate ./db2cli validate -dsn "db2dsn" -connect -user "****" -passwd "****", I get a warning

Warning: Unable to retrieve information on various IBM Data Server Client Packages on the current workstation. Continuing with remaining validation process.

And it does not connect. No errors are thrown, and the log (\db2diag.log) is empty.When I try to execute a SQL script (SELECT * FROM SomeTable), there's no output, and no errors:

PS C:\DB2_drivers\clidriver\bin> ./db2cli execsql -dsn db2sn -inputsql sql.sqlIBM DATABASE 2 Interactive CLI Sample Program(C) COPYRIGHT International Business Machines Corp. 1993,1996All Rights ReservedLicensed Materials - Property of IBMUS Government Users Restricted Rights - Use, duplication ordisclosure restricted by GSA ADP Schedule Contract with IBM Corp.

Dockerfile (where the DB2 ODBC drivers are unpacked into ./DB2_drivers)

# escape=`FROM mcr.microsoft.com/windows:1903 SHELL ["powershell"]#install DB2 ODBC driverCOPY ./DB2_drivers/ ./DB2_drivers/WORKDIR DB2_drivers/clidriver/bin/RUN ./db2cli install -setup; ` ./db2cli writecfg add -dsn "db2dsn" -database "SAMPLE" -host "192.168.1.10" -port "50000"; ` ./db2cli registerdsn -add "db2dsn" -system

Base Images: I've tried, without success, on Windows 10 1903, Windows Server Core LTSC 2019, and microsoft/iis

What Works

When I setup the DSN and drivers as above on Windows 10 and Windows Server 2016 (VMs or locally), and then run the validate or executesql commands as shown above, everything works fine. The connection succeeds, and I get the output from the SELECT in sql.sql.

When I try to do the same in the ~mostly~ same environment but in Docker, it fails, even though the DSN appears to have been successfully created (Get-OdbcDsn shows the DSN). I can ping the DB2 server from the Docker container, so it's not a firewall issue.

Question

Is the ODBC driver not setup correctly in Docker, or is there some environmental issue with DB2CLI and Windows Docker containers? Without the db2diag.log, I'm not sure how to find what's wrong.

BinaryString posted from angular corrupted in node js blob file object after save in ibm db2 blob field

I'm using HTML 5 file reader to read the uploaded file and save it in a database as BLOB. And fetch this BLOB data and provide the download link in my application.

If I upload a PDF file and download, it works in my local machine. But same code not working in server, the downloaded PDF is empty

Code in angular to read the file and post the data to node.js

const reader = new FileReader();reader.onloadend = (e) => { const content = reader.result; var dotSplit = file.name.split("."); const fileObject = { name: file.name, lastModified: file.lastModified, size: file.size, type: file.type, lastModifiedDate: file.lastModifiedDate, mimetype: dotSplit[dotSplit.length - 1] }; files.push({ file: fileObject, content: content }); //which will be posted to node js using http.post method};reader.readAsBinaryString(file);

Code in node.js to save the data

const content = atts.content; //content sent from Angularvar fileObj = { ParamType: 1, DataType: "BLOB", "Data": content };var qry = "INSERT...";var params = [];params.push(resReqId, fileObj, atts.file.name, atts.file.mimetype, 1, '', 'REQ');

Code n Angular to convert the BLOB data and download

var blob = new Blob([this.str2ab(response.result.content)], { type: response.result.type + ';charset=UTF-8;' });var link = document.createElement('a');link.href = window.URL.createObjectURL(blob);link.download = response.result.filename;document.body.appendChild(link);link.click();result.next(response);str2ab(str) { const buf = new ArrayBuffer(str.length); const bufView = new Uint8Array(buf); for (let i = 0; i < str.length; i++) { bufView[i] = str.charCodeAt(i); } return buf;}

In database the PDG file saved as BLOB(73630) 255044462D312E330A25C4E5.... if I try in local. In angular filesize shows 72 KB.

Same PDF file saved as BLOB(107262) 255044462D312E330A25C384... if I try in server. In angular filesize shows 105 KB.

Looks like the file corrupted while saving in the database. What could be the reason for this?

Db2 dynamic batch execution

Set ls_str='call batch_document(input1,input 2,?,?)';

How to execute tis dynamic query execution in db2.

I tried ,Prepare sql_statement from ls_str.

It not works.

How to override schema use on ibm_db.exec_immediate(connect, sql) in Python

I am trying to run a run select count(*) from 'ActualSchemaUser.TABLE_NAME' where 'DATE_CREATE' >= 2019-10-17 and I get the following error:

**Exception: [IBM][CLI Driver][DB2/LINUXZ64] SQL0204N "DatabaseUSer.ActualSchemaUser.TABLE_NAME" is an undefined name. SQLSTATE=42704 SQLCODE=-204**

It looks like ibm_db.exec_immediate(connect, sql) adds the database connecting user to the query from ibm_db module perspective.

Is there a way I can prevent set or override the schema for ibm_db.exec_immediate() function.

Python code snippet:

import ibm_dbconnect = ibm_db.connect("DATABASE=DatabaseName;HOSTNAME=DatabaseHostname;PORT=DatabaseName;PROTOCOL=TCPIP;UID=DatabaseUSer; PWD=DataBasePassword;", "", "")schemaName = 'ActualSchemaUser'sql = "select count(*) from '{}.TABLE_NAME' where 'DATE_CREATE' >= 2019-10-17".format(schemaName)statement = ibm_db.exec_immediate(connect, sql)result = ibm_db.fetch_assoc(statement)print(result)
Need an SQL script to update a new column with the values concatenated from 3 columns of the same table

I need a prepare an SQL script to be given to my production support team to run this in production. We have created a new column in the DB2 table. This column should be filled with the data by concatenating 3 column values of the same table in the same row.

To give a history, all the reason text which are entered in the front end are inserted into the request table by unstringing into 3 columns. Since they had limited length, we created a new column with increased length and going forward all insert will go into the new column. But we need to move all the existing data in the old 3 columns to his new one. S this SQL update is just an one time exercise.

Table: tab_request

enter image description here

We added new column to have a increased character length and to align with other table nomenclature. Now I need the script to update the column reasontext as below

enter image description here