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 convert date from BD2 format to string?

I want to convert

SELECT CURRENT DATE FROM sysibm.sysdummy1;

to 20180119 (YYYYMMDD) rather than with the hyphen 2018-01-19 (YYYY-MM-DD)

C# - Cannot implicitly convert type 'IBM.Data.DB2.iSeries.iDB2DataReader' to 'System.Data.SqlClient.SqlDataReader'

I am working in a Ranorex project, but it isn't a Ranorex specific problem. I need to open a connection to an AS400 DB2 Database and then run a SQL query against it.

I have code that successfully opens the connection and returns the results of the SQL query.

iDB2Connection cn = new iDB2Connection("Data Source=as400_DB;User ID=CLIENT;Password=CLIENT;Default Collection=XXXXXX;Naming=System"); cn.Open(); iDB2Command cmd = new iDB2Command("select count(*) from ABC_table where xxxx = 'WC' AND xxxx = 'L302328'", cn); int count = Convert.ToInt32(cmd.ExecuteScalar()); Report.Log(ReportLevel.Info, "count", count.ToString()); cn.Close(); 

In the code, the second method, calls the first method to open the connection, which then allows the second method to run the SQL commands, but I get the following error:

Cannot implicitly convert type 'IBM.Data.DB2.iSeries.iDB2DataReader' to 'System.Data.SqlClient.SqlDataReader'

at the 'return new iDB2Connection(cn.ToString())' point in the first method.

 public static class sQlHelper{ private static SqlConnection sQlConnect() { iDB2Connection cn = new iDB2Connection("Data Source=as400_DB;User ID=CLIENT;Password=CLIENT;Default Collection=XXXXXX;Naming=System"); return new iDB2Connection(cn.ToString()); } public static void validateResult() { var myConnection = sQlConnect(); myConnection.Open(); string sqlStatementForCheckHeaders = "select count(*) from ABC_table where xxxx = 'WC' AND xxxx = 'L302328'"; int count = Convert.ToInt32(cmd.ExecuteScalar()); SqlDataReader myReader = null; iDB2Command myCommand = new iDB2Command(sqlStatementForCheckHeaders, myConnection); myReader = myCommand.ExecuteReader(); while(myReader.Read()) { Console.WriteLine(myReader["Column1"].ToString()); Console.WriteLine(myReader["Column2"].ToString()); } myConnection.Close(); }}

I can't workout how to resolve the issue and I have not been able to find any information on the error itself. I would be grateful for any support/advice people can supply.

How to find transactions with same transacrionTo

Let's say we have a table called Transaction. Each transaction has an id(key), a transactionFrom(number) and a transactionTo(number). How can I find all the transactions that have for the same transactionTo but different transactionFrom? Any ideas? Thanks!

Cannot use column names in SQL queries of DashDB/"Db2 Warehouse on Cloud"

I loaded some sample data into Db2 Warehouse on Cloud but I'm not able to execute even basic SQL queries since I'm always getting "QL0206N: SQL0206N "[column_name]" is not valid in the context where it is used. SQLSTATE=42703".

for example: select log_id from m1

I tried multiple variants like select m1.log_id from m1 select m1.log_id from dash6792.m1 select message.log_id from dash6792.m1 as message => still getting the same error.

I also tried different columns and tried to put them into different places of the query (e.g. in "where" or "on" clauses) => the same result.

Surprisingly, I can query tables from sample schemas (e.g. "GOSALES") normally => so the problem occurs only for my schema (but for all tables).

Locate where is the nth occurrence of a token in a string separated by pipes

I'm I am a newbie with Regex and would like to know if it is possible to do that.

It is possible to locate the token position of a sub-string in a string like the below sample text?

AA|BBBBBBBBBB|XXXX||XXXX||FFFFFFFFFFF

Requesting the position of the 1st occurrence of 'XXXX' I must get '3', requesting the 2nd occurrence of 'XXXX' I must get '5', requesting the 3rd occurrence of 'XXXX' I must get '0' cause there's no a 3rd ocurrence.

This can be done using just regex?

Thanks in advance.

PS: If it is possible I will implement this solution on DB2 v7r2 using REGEX functions to replace an UDF I write long time ago on PLSQL to do this job.

DB2 backup with BLOB fields

Majority of the DB2 space is occupied by fields of the type BLOB and CLOB. Currently, full backup of the entire database takes very long time and goes beyond time limits. We tried to use incremental backup to speed up backup, but problem did not go away as BLOB and CLOB fields are still being pulled into the backup, irrespective of whether they were changed, so it is almost the same as doing a full backup.

We decided to proceed with following approach:Create a new tablespace and put there tables with BLOBs and CLOBs. Old data will be archived once a year, and this tablespace every day.Once the size of tablespace exceeds a certain limit, we will create a new space and start writing new data to it, and so on.

Problem occurs with restoration when using above mentioned approach.

Backup steps:

  1. Full backup of all data is done.
  2. Logs are copied.
  3. Backup of a separate tablespace is done.
  4. Logs are copied.

Restoration approach:

  1. Full backup is restored.

  2. Logs are copied and ROLLFORWARD performed. So far everything is OK.

  3. Backup of a separate tablespace is restored.

  4. Logs are copied. !!But ROLLFORWARD does not work anymore.The following errors were received: SQL4908N, SQL4906N, SQL1272N.

Is this feasible approach for backup and are steps we take to backup and restore adequate?Is there a better way to speed up backup, while still maintaining same level of resilience?

Connect Python Flask web server application to DB2

I have a python flask Bluemix/IBM cloud-application for which I would like to use to move data from Cloud object storage to DB2 Warehouse on Cloud.

To make it easier I started by creating this code in a Data Science Experience Notebook, which works perfectly. To connect to DB2 I am using the IBMDBPY-package. Now I am starting to migrate my code from the Notebook to the python flask application.

The connection to Cloud object Storage works, but when I want to connect it to DB2 it fails due to

"IdaDataBaseError: ibmdbpy::IdaDataBaseError: SQL_ERROR"

To be able to connect this locally I need to install a JDBC driver for DB2 which I can't because it is not supported on a Mac. According to the IBMDBPY-guide I have to upload two files from the JDBC driver to the directory where the IBMDBPY-package exists. But Because the packages are imported from the requirements.txt in the Flask-application I can't really put them in that folder.

Is there any solutions or workarounds regarding this? If I can only make it work when the application is running on the server and not locally that is fine as well. As long as everything works fine when application is running.

Process Each row in stored procedure or SQL in DB2

I am basically from Oracle background and trying to create a SQL code or stored procedure which will execute REORG for each table which is in reorg pending status

I have already figured out way to get list of such tables as given below

SELECT 'CALL SYSPROC.ADMIN_CMD(''REORG TABLE', substr(rtrim(TABSCHEMA)||'.'||rtrim(TABNAME),1,20) ||''')', ';' from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y' AND TABSCHEMA=CURRENT SCHEMA

Now my requirement is to run this result set as a DB2 SQL command for each row found, I don't have option to use shell script or batch file , it has to be done using SQL or stored procedure. I tried creating proc but lots of issues with that , can someone pl help

Golang db2cli running select query gets cgo error

I'm trying to use db2cli library (https://bitbucket.org/phiggins/db2cli/) to implement DB2 queries (select, insert, delete). But get CGO error when trying to run select queries. Here is my code:

db, err := sql.Open("db2-cli", *connStr)if err != nil { return err}defer db.Close()rows, err := db.Query("select * from tab")

The error is:

panic: runtime error: cgo argument has Go pointer to Go pointergoroutine 1 [running]:bitbucket.org/phiggins/db2cli/api.SQLBindCol.func1(0x9000100010001, 0xc420062238, 0xa00000006, 0xc420062218, 0x5b00000040)

......

If I manually run the select queries from DB2 command line, it will return correct results. And other type of queries (insert, delete etc.) with Go language code also work correctly. I tried to set GODEBUG=cgocheck=0, but still get the same issue. Has anyone encountered similar problems when using db2cli?

How to create a user defined function that returns a table in a DB2 module?

I am trying to create a user-defined function that returns a table in DB2. Here is what I have so far.

This is a table that I use:

CREATE TABLE "CORPDATA"."EMPLOYEE" ( "EMPNO" CHAR(6) NOT NULL, "FIRSTNME" VARCHAR(12) NOT NULL, "MIDINIT" CHAR(1) NOT NULL, "LASTNAME" VARCHAR(15) NOT NULL, "WORKDEPT" CHAR(3), "PHONENO" CHAR(4), "HIREDATE" DATE, "JOB" CHAR(8), "EDLEVEL" SMALLINT NOT NULL, "SEX" CHAR(1), "BIRTHDATE" DATE, "SALARY" DECIMAL(9 , 2), "BONUS" DECIMAL(9 , 2), "COMM" DECIMAL(9 , 2));ALTER TABLE "CORPDATA"."EMPLOYEE" ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY("EMPNO");

This is a user-defined function that returns a table (which is working fine):

CREATE OR REPLACE FUNCTION "CORPDATA"."DEPTEMPLOYEES" (DEPTNO CHAR(3)) RETURNS TABLE (EMPNO CHAR(6), LASTNAME VARCHAR(15), FIRSTNAME VARCHAR(12)) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC RETURN SELECT EMPNO, LASTNAME, FIRSTNME FROM CORPDATA.EMPLOYEE WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO; END

This is a how far I was able to get with the module:

CREATE MODULE CORPDATA.MODULE1ALTER MODULE CORPDATA.MODULE1PUBLISH FUNCTION DEPTEMPLOYEES2 (DEPTNO CHAR(3))RETURNS TABLE (EMPNO CHAR(6), LASTNAME VARCHAR(15), FIRSTNAME VARCHAR(12))

Any attempts to actually add a function to a module failed with various errors. Here is my DB2 version information: Database server = DB2/LINUXX8664 11.1.2.2This is an Express-C installation under Redhat.

When I try this, I get SQL0628N Multiple or conflicting keywords involving the "RETURNS" clause arepresent. LINE NUMBER=16. SQLSTATE=42613

ALTER MODULE corpdata.module1ADD FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3)) RETURNS TABLE (EMPNO CHAR(6), LASTNAME VARCHAR(15), FIRSTNAME VARCHAR(12)) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTICBEGIN ATOMIC RETURN SELECT EMPNO, LASTNAME, FIRSTNME FROM CORPDATA.EMPLOYEE WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO;END

When I try this (removed RETURNS clause), I get SQL0491N The CREATE FUNCTION or ALTER MODULE statement used to define"CORPDATA.MODULE1.DEPTEMPLOYEES" must have a RETURNS clause, and one of: theEXTERNAL clause (with other required keywords); an SQL function body; or theSOURCE clause. LINE NUMBER=8. SQLSTATE=42601

ALTER MODULE corpdata.module1ADD FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3)) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTICBEGIN ATOMIC RETURN SELECT EMPNO, LASTNAME, FIRSTNME FROM CORPDATA.EMPLOYEE WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO;END

When I try this (removed BEGIN ATOMIC), I get SQL0104N An unexpected token "SELECT" was found following "INISTICRETURN ". Expected tokens may include: "(". LINE NUMBER=9. SQLSTATE=42601:) Yes, it does say "INISTIC".

ALTER MODULE corpdata.module1ADD FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3)) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC RETURN SELECT EMPNO, LASTNAME, FIRSTNME FROM CORPDATA.EMPLOYEE WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO