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 Driver/Datasource setup on wildfly: Failed to load module for driver [com.ibm]

I am wanting to configure the data source for db2 on my wildfly server (Wildfly.8.0.0-Final and 8.1.0 as well.) and am running into some problems doing so.

My research tells me this is a two step process

  1. install the drivers as a module in the %JBOSS_HOME%/modules/com/ibm/main dir.
  2. configure the datasources subsystem to include this module as a driver in your connection settings.

So far I have installed the module under the following structure with the following module.xml:

modules/`-- com/ `-- ibm/ `-- main/ |-- db2jcc4.jar |-- db2jcc_license_cu.jar |-- db2jcc_license_cisuz.jar `-- module.xml          

There is no space before the in the xml file. the module name is "com.ibm" and the datasource is as follows:

   myIP 1234 MyDB 4 ibmdb2  0 50   bob isyouruncle          com.ibm.db2.jcc.DB2XADatasource   

The loading up of the server produces this error:

12:49:01,228 ERROR [org.jboss.as.controller.management-operation] (ServerService Thread Pool -- 9) JBAS014613: Operation ("add") failed - address: ([ ("subsystem" => "datasources"), ("jdbc-driver" => "ibmdb2")]) - failure description: "JBAS010441: Failed to load module for driver [com.ibm]"

Which in turn causes my datasource declaration to fail loading as the driver is missing.

I am using older documentation as a guide because there doesn't seem to be any available for wildfly as yet. this documentation shows some promise but it seems a little out of date. If anyone has had any experience setting this up then Your help would be much appreciated.

I want to connect to DB2 9.7.

Please and thank you.

No authorized routine named "SYSPROC.CE_UPDATE_DETAILS" of type "" having compatible arguments was found. .NET CORE-DB2

I am trying to run DB2 Stored procedure from .NET Core 1.0 on a windows machine. but facing exception as mentioned in title. when i mention simple select statement in command text , it works fine.

 conn.Open(); DB2Command command = conn.CreateCommand(); DB2Parameter db2Param = new DB2Parameter(); db2Param.ParameterName = "IN_ACCT_NUMBER"; db2Param.DB2Type = DB2Type.VarChar; db2Param.Direction = ParameterDirection.Input; db2Param.Value = "123456"; command.Parameters.Add(db2Param); DB2Parameter SQLCD = new DB2Parameter("OUT_SQLCD", DB2Type.Char, 5); SQLCD.Direction = ParameterDirection.Output; command.Parameters.Add(SQLCD); command.CommandText = "SYSPROC.CE_UPDATE_EXCLUSIONS"; command.CommandType = CommandType.Text; DbDataReader drReader = command.ExecuteReader();
Select a row with Max Date for set of account number

I have a table with three columns, ACCT_NUM, EFF_DATE and ACCT_NAME. Unique keys are ACCT_NUM and EFF_DATE.

Eg Data,

ACCT_NUM EFF_DATE ACCT_NAME0000100200 2017-01-01 Account 10000100200 2017-03-10 Account A0000100200 2017-08-22 Account Alpha0000100230 2017-01-01 Account C0000100230 2017-05-20 Account Charlie

and more accounts.

Want to get latest account name based of Eff_date for a set of account numbers using in clause.

select * from ACCT_MASTER where acct_num in ('0000100200', '0000100230') order by eff_date fetch 1 row only; -- but this query returns only one account

like to get following data,

ACCT_NUM ACCT_NAME0000100200 Account Alpha0000100230 Account Charlie

Convert dd-mon-yy string to date in DB2

in DB2 I've got DATE values stored as varchar in the form 'DD-Mon-YY' (e.g. 25-Jun-13). I'd like to convert these into DB2 compatible date formats using the TO_DATE function but every date conversion format I've tried gives me an error.

e.g. I've tried to_date('25-Jun-13', 'YYYYMMDD') and to_date('25-Jun-13', DDMMYYYY) and I always get something like "25-Mar-13" cannot be interpreted using format string "DDMMYYYY" for the TIMESTAMP_FORMAT function.

Does anyone know if there is a format string that I can use?

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
Is there a way to Concat multiple rows into one but keep each column value as a seperate column?

I know that it is possible to almost do what I want with LISTAGG however that adds all of the values into one column.

For example if I have something like this

SubjectID StudentName---------- -------------1 Mary1 John1 Sam2 Alaina2 Edward

I am hoping to get something like this

SubjectID StudentName StudentName1 StudentName2 ---------- ----------- ------------ ------------1 Mary John Sam 2 Alaina Edward 0
How to write DB2 SELECT statement in unload job for delimiters

I have been over this... seems silly but couldnt figure out!

I wanna UNLOAD a table but with a delimiter '|' in between the fields. Here is the JCL used to unload the table:

//JS020 EXEC PGM=IKJEFT01, // DYNAMNBR=20 //* //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSPUNCH DD SYSOUT=* //* //SYSTSIN DD * DSN SYSTEM(XXXX) RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL')//* //SYSREC00 DD DSN=TABLEA.UNLOAD.FILE, // DISP=(NEW,CATLG,DELETE), // UNIT=SYSDA,LRECL=80 //SYSIN DD * SELECT COLUMN1 ,'|',COLUMN2 ,'|',COLUMN3 ,'|',COLUMN4 ,'|',COLUMN5 FROM TABLEA WITH UR; /* //* 

Output yields

VALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEEVALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEEVALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEEVALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEEVALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEE

But I wish to have like below

VALUEA|VALUEB|VALUEC|VALUED|VALUEEVALUEA|VALUEB|VALUEC|VALUED|VALUEEVALUEA|VALUEB|VALUEC|VALUED|VALUEEVALUEA|VALUEB|VALUEC|VALUED|VALUEEVALUEA|VALUEB|VALUEC|VALUED|VALUEE

I'm not able to figure our why that .. is preceeding the delimiter '|'. Any guesses what does that value mean? Thanks for your interest.

SQL like with multiple characters

I'm trying to perform some selects in DB2 SQL, using the LIKE command, specifically looking for values that start with certain multiple digits. I thought I was using the expression correctly, but I keep getting syntax errors and I'm not sure where I'm going wrong.Right now I've been trying:

Select ID, NAMEFrom Table1Where ID LIKE '888%'Or ID LIKE '999%';

Am I just using the wrong SQL for the environment? (This is polling DB2 for Zo/s) LIKE doesn't seem to work when I just try a single 8% or 9% either.

I appreciate any shared experience, thank you!

SQL Query to concat duplicated row

Let's say I have a table with 3 columns with the following values


| A | B | C || A1| B1| C || A | B | C1|_____________

I'd like to make a query to get

A | B | C, C1|A1| B1| C|

to get distinct First & Second Column. Any help would be greatly appreciated

db2 prune history timestamp and delete having no effect?

I need your help with this one: I'm on db2 luw 10.5.8 and have set logarchmeth1 to tsm. I noticed my logpath is getting full (only 10% of available space left) and so I decided to use the db2 prune history command to free up some space.

Here is the command I used:

db2 prune history 20170813 and delete

I was under the impression that this command would free up some space by deleting all logfiles prior or equal to the specified date. Unfortunatley it didn't work that way. The available free space in my logpath remained unchanged at 10%.

I also tried the depracated prune logfile command (same result):

db2 prune logfile prior to S0000100.LOG

What can I do to free up some space in my logpath?Any ideas?