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.

 

 
look up any number in a text field

I have a column that has plain text data. I am looking to find any name that is followed by an integer 'Sam 123', Sam 597" etc.

I have tried

SELECT message, LENGTH(message)FROM surveysWHERE LENGTH(MESSAGE) = 6 

but it returns alphanumeric data.

DB2: How to filter based on the result obtained from CASE condition

I am trying to filter the data based on the results obtained from previous CASE condition in DB2:

Below is the initial query to filter the data:

SELECT EM.NAME,EM.ID,EM.DIVCASE DEPTWHEN '1' THEN 'FIN'WHEN '2' THEN 'MKT'WHEN '3' THEN 'IT'WHEN '4' THEN 'HR' END AS DEPT_DESCFROM EMPLOYEE_RCD EM;

Sample Data:

NAME ID DIV DEPT_DESCMICHAEL 3334 3 ITSAMUEL 100922 1 FINORINDA 7363 1 FINSARA 8383 2 MKTRACHAEL 8383 4 HRMARTIN 33312 SUZY 993 NA NAMIKE 576 NULL NULL

What I tried to filter the results:

SELECT EM.NAME,EM.ID,EM.DIVCASE DEPTWHEN '1' THEN 'FIN'WHEN '2' THEN 'MKT'WHEN '3' THEN 'IT'WHEN '4' THEN 'HR' END AS DEPT_DESCCASE DEPT_DESCWHEN DEPT_DESC == '' THEN 'No Dept'WHEN DEPT_DESC == NA THEN 'No Dept'WHEN DEPT_DESC == NULL THEN 'No Dept'FROM EMPLOYEE_RCD EM;

but not getting the results instead getting errors:

[Code: -104, SQL State: 42601] An unexpected token "== ''" was found following "WHEN DEPT_DESC". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.22.29

Any help in, how to filter the data and get the below result:

Expected 0/P:

NAME ID DIV DEPT_DESCMARTIN 33312 SUZY 993 NA NAMIKE 576 NULL NULL
DATEDIFF in DB2 query

I have a query from mysql that has been running on a table I recently migrated to DB2.

The query now fails on DB2 due to the line below, saying that DATEDIFF cannot be found. I'm assuming only because this isn't a valid function on db2.

Is there an equivalent to this on DB2 that will maintain performance as well as function?

SELECT DISTINCT LEAST(180, DATEDIFF(curdate(), start_date)) as daysFROM table2where expire_date > curdate()
Query in sql on db2 database

I'm using this query:

select substr("Message_Time",6,2) || '/' || substr("Message_Time",4,2) || '/' || '20'|| substr("Message_Time",2,2) || substr("Message_Time",8,2) || ':' || substr("Message_Time",10,2) as "Date", count(*) as "Fault", "Message_Location", "Service_Name_U", "Operation_Name_U", "Port_Namespace_U", "Error_Code_U", "Error_SubCode_U", "Fault_Code_U", "Fault_String_U", "Requester_Identity", "Application_ServerName_U"from "Fault_Log_Table_610"where "Message_Time" >= 1181016220000000 and "Message_Time" < 1181017220000000 and "Operation_Name_U" = 'getDomandeDisabile'group by substr("Message_Time", 6, 2) || '/' || substr("Message_Time", 4, 2) || '/' || '20'|| substr("Message_Time", 2, 2) || substr("Message_Time",8,2) || ':' || substr("Message_Time",10,2), "Service_Name_U", "Operation_Name_U", "Error_Code_U", "Error_SubCode_U", "Message_Location", "Fault_Code_U", "Fault_String_U", "Port_Namespace_U", "Requester_Identity", "Application_ServerName_U"

I need to add 2 hours to Date field, in other words I need to add the number 2 to substr("Message_Time",8,2).

The date has the format 1181020164532000 where:

first number is centurythen following two numbers are the yearsthen following two numbers are the monthsthen following two numbers are the daysthen following two numbers are the hoursthen following two numbers are the minutesand last three numbers are milliseconds

Cannot connect to fresh installed DB2 on WINDOWS 10

I just installed a DB2 on my Windows 10 laptop.

Installation looks ok.

If I run db2level I get this answer:

C:\IBM\SQLLIB\BIN>db2licm -lNome prodotto: "IBM DB2 Developer-C Edition"Tipo di licenza: "Comunità "Data scadenza: "Permanente"Identificativo prodotto: "db2dec"Informazioni sulla versione: "11.1"Quantità massima di memoria (GB): "16"Numero massimo di core: "4"Numero massimo di tablespace (GB): "100"

When I run db2licm -l I get this:

C:\IBM\SQLLIB\BIN>db2licm -lNome prodotto: "IBM DB2 Developer-C Edition"Tipo di licenza: "Comunità "Data scadenza: "Permanente"Identificativo prodotto: "db2dec"Informazioni sulla versione: "11.1"Quantità massima di memoria (GB): "16"Numero massimo di core: "4"Numero massimo di tablespace (GB): "100"

DB2 is started, I can issue db2stop and db2start command without any issue.

And now the PROLBLEM: I cannot find a way to CONNECT to the DB2 instance.In fact when I run CONNECT command CLPPlus window freezes this way:

CLPPlus window

while RAM usage start rising:

DB2 RAM usage

I googled IBM sites without finding any suitable solution.

Has anyone experienced the same problem?

Thanks a lot

Ettore

How to calculate days between two date columns in db2?? how do i correct the difference of days in this query?

my query :

SELECT CD1, OD1,CHAR(DATE(SUBSTR(CHAR(CD1),1,4) ||'-'|| SUBSTR(CHAR(CD1),5,2) ||'-'|| SUBSTR(CHAR(CD1),7,2)), USA)AS "CDate_Conversion",CHAR(DATE(SUBSTR(CHAR(OD1),1,4) ||'-'|| SUBSTR(CHAR(OD1),5,2) ||'-'|| SUBSTR(CHAR(OD1),7,2)), USA)AS "OConv", CD1-OD1 AS DaysFROM PDAT.ZPKD

Output:

Crt Date Date Ordered CDate_Conversion OConv DAYS20171201 20171130 12/01/2017 11/30/2017 7120171003 20170929 10/03/2017 09/29/2017 7420171009 20170908 10/09/2017 09/08/2017 10120171009 20170921 10/09/2017 09/21/2017 8820171002 20170929 10/02/2017 09/29/2017 7320171009 20171006 10/09/2017 10/06/2017 320181010 20181010 10/10/2018 10/10/2018 020180723 20180723 07/23/2018 07/23/2018 020180710 20180709 07/10/2018 07/09/2018 120181010 20181009 10/10/2018 10/09/2018 120180831 20180830 08/31/2018 08/30/2018 120180827 20180814 08/27/2018 08/14/2018 1320180828 20180827 08/28/2018 08/27/2018 120180403 20180403 04/03/2018 04/03/2018 020180405 20180403 04/05/2018 04/03/2018 220180820 20180820 08/20/2018 08/20/2018 020180920 20180919 09/20/2018 09/19/2018 120180305 20180305 03/05/2018 03/05/2018 020180306 20180305 03/06/2018 03/05/2018 1

The difference of days is incorrect in first 5 rows.

also,i am not sure how to find difference of days with the converted columns so instead i chose CTD-OTD !

and there is no time column for OTD, so cant use TIMESTAMPDIFF .

note : i randomly selected the rows with wrong difference of days to show the resuly=ts here.

Event Monitor in DB2 log the details of query run from VPN on the same server where events are created?

Events has been created on my database. If anybody take the VPN of my DB and do some tasks. These activities logged or not.If not then how we can do that.

Adding IBM Db2 as a datasource

Has anyone had any luck or found documentation on how to add IBM Db2 as a datasource for their dashboard on Apache Superset? I don't see any information in the Db2 service credentials about the driver or dialect.

SQL0443 error when executing a Select with a Trigger on DB2 for iSeries

I've some problem querying a db2/as400 table, let's call it TAB1. Since it was added a trigger on this table when I perform a normal SELECT (using the TAB1-key) I get the following error. Never had a problem before the trigger was created.

It's a query performed in a batch-application (Java 1.6) using Modern Batch and Spring Batch 2.1.8. No chance to update the libraries, since the program is quite old and the customers won't agree.

Anyway,I would say it's a trigger problem ( as the sql codes say) but different applications perform different SELECTs on TAB1 and they never get any similar problem.The batch perform more or less 40000 select like this, and just 300 fail with this error.

Any idea, tip, suggestion?

### Error querying database. Cause: java.sql.SQLException: [SQL0443] *N *N ### The error may exist in class path resource [eu/mycompany/el20/dq/as400/dataaccess/mappers/tab1/Tab1Mapper.xml] ### The error may involve eu.mycompany.el20.dq.as400.dataaccess.persistence.tab.Tab1Mapper.selectByExample-Inline ### The error occurred while setting parameters ### SQL: select * from TAB1 WHERE ( D10_SOC = ? and D10_COD_NDG = ? and D10_DATE = ? ) ### Cause: java.sql.SQLException: [SQL0443] *N *N ; uncategorized SQLException for SQL []; SQL state [38501]; error code [-443]; [SQL0443] *N *N; nested exception is java.sql.SQLException: [SQL0443] *N *Nstack trace: org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71) org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:365) com.sun.proxy.$Proxy120.selectList(Unknown Source) org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:195) org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:124) org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:90) org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40) com.sun.proxy.$Proxy136.selectByExample(Unknown Source) eu.mycompany.el20.dq.as400.crud.services.tab.BLSTab1.select(BLSTab1.java:46) it.mycompany.xframe.dq.batch.steps.programstep.ProgramExecutor.processRecord(ProgramExecutor.java:544) com.ibm.websphere.batch.devframework.steps.technologyadapters.GenericXDBatchStep.processRecord(GenericXDBatchStep.java:263) com.ibm.websphere.batch.devframework.steps.technologyadapters.GenericXDBatchStep.processJobStep(GenericXDBatchStep.java:227) com.ibm.ws.gridcontainer.batch.impl.StepManagerImpl._regularJobBatchLoop(StepManagerImpl.java:1065) com.ibm.ws.gridcontainer.batch.impl.StepManagerImpl.executeStep(StepManagerImpl.java:390) com.ibm.ws.gridcontainer.security.actions.ExecuteStepBatchUserPrivilegedAction.executeAction(ExecuteStepBatchUserPrivilegedAction.java:47) com.ibm.ws.gridcontainer.security.AbstractUserPrivilegedAction.runWithoutSecurity(AbstractUserPrivilegedAction.java:66) com.ibm.ws.gridcontainer.services.impl.WASRunUnderCredentialServiceImpl.runUnderUserCredential(WASRunUnderCredentialServiceImpl.java:134) com.ibm.ws.gridcontainer.services.impl.WASRunUnderCredentialServiceImpl.runActionUnderUserCredential(WASRunUnderCredentialServiceImpl.java:386) com.ibm.ws.gridcontainer.batch.impl.JobManagerImpl._sequentialStepScheduling(JobManagerImpl.java:783) com.ibm.ws.gridcontainer.batch.impl.JobManagerImpl.executeJob(JobManagerImpl.java:199) com.ibm.ws.batch.BatchJobControllerWork._runJob(BatchJobControllerWork.java:435) com.ibm.ws.batch.BatchJobControllerWork.run(BatchJobControllerWork.java:241) com.ibm.ws.asynchbeans.J2EEContext$RunProxy.run(J2EEContext.java:271) java.security.AccessController.doPrivileged(AccessController.java:399) com.ibm.ws.asynchbeans.J2EEContext.run(J2EEContext.java:797) com.ibm.ws.asynchbeans.WorkWithExecutionContextImpl.go(WorkWithExecutionContextImpl.java:222) com.ibm.ws.asynchbeans.ABWorkItemImpl.run(ABWorkItemImpl.java:206) java.lang.Thread.run(Thread.java:790)
create/remove row permission in IBM i (as400) SQL

In Db2 for IBM i, I can create row permission like this.

-- enable row access controlALTER TABLE QIWS.QCUSTCDT ACTIVATE ROW ACCESS CONTROL;-- create permission MYPERMISSION999CREATE PERMISSION MYPERMISSION999 ON SALES.ORDHEDFOR ROWS WHERE VERIFY_GROUP_FOR_USER(SESSION_USER,'MYGROUP') = 1 AND SCODE = SESSION_USER ENFORCED FOR ALL ACCESSENABLE;COMMIT;ALTER TABLE SALES.ORDHED ACTIVATE ROW ACCESS CONTROL;COMMIT;

If I Deactivate row access control, rules seem to go away, but if I ACTIVATE old rules come back.

 ALTER TABLE SALES.ORDHED DEACTIVATE ROW ACCESS CONTROL