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 to USE WHERE IN Clause DB2 in SSRS reports (Multi value parameter)

I am passing multi value parameter in SSRS querying in DB2. But it gives me error whenever I select multi selection in reports. I believe WHERE IN Clause not working in ssrs while querying in DB2. I have 4 parameters and 2 of them are multi selection.

How to convert VARCHAR to TIMESTAMP in DB2

I have a varchar value in BD2 Table like below format

121226145503+0530

I want to convert this varchar value to time stamp format like below

2012/12/26 14:55:03
Insert into from select with multiple columns and multiple tables

I need to create a SQL Trigger with Insert Statement selecting multiple columns from different tables . Something like this:

CREATE OR REPLACE TRIGGER ACTIV_TRG AFTER INSERT ON TRANS_LOGREFERENCING NEW AS TR FOR EACH ROW MODE DB2SQLBEGIN ATOMIC INSERT INTO ACTIVITIES values ( (SELECT MAX(SEQNBR) FROM ACTIV_LOG where TRAN_ID = 'XXX'), TR.ACCOUNT, TR.EVENT, SELECT TRAN_ID , TRAN_DT , TRAN_LN FROM ACTIV_DEL where TRAN_ID = 'XXX', CURRENT TIMESTAMP)END;

The issue is when I tried to select more than one column in the sub query. I know if I separate the Sub Query like this :

(TRAN_ID FROM ACTIV_DEL where TRAN_ID = 'XXX'),(TRAN_DT FROM ACTIV_DEL where TRAN_ID = 'XXX'),(TRAN_LN FROM ACTIV_DEL where TRAN_ID = 'XXX'),

will work but definitely there must be a better and efficient way to do this.

Thanks in advance.

db2 replace function not working when called though the CALL SYSPROC.ADMIN_CMD [on hold]

Am running my db2 select query through CALL SYSPROC.ADMIN_CMD and it fails however when i run the select query directly in DB explorer it works fine. SELECT REPLACE(ACCT_NUM_SUFFIX,';','')there is something i have to take care of the quotes but am not getting that.Any advise please

How to display data from DB2 database with SQL query in php

I'm using php to build a simple front end web application to my db2 database. This php query connects to the database and pulls the data from my selected table. I'm trying to insert my data into an html table to display it better (the rows are showing up in array formatting right now due to the "db2_fetch_array" function. How do I put my data into an html table? My php code is below, what should I add? Most questions I could find only dealt with mySQL and didn't have the same specification as I do.

DB Testing";} else{ exit("failed".db2_conn_errormsg()); }$sql = "select 'JUNK', apple, banana, orange, cake, grapes, egg from kitchen";//db2_execute executes a sql statement that was prepared by db2_prepareif($stmt){ $result = db2_execute($stmt); if(!$result){ echo "exec errormsg: " .db2_stmt_errormsg($stmt); } echo '';while($row = db2_fetch_array($stmt)) { echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; }echo '
' . $row['apple'] . '' . $row['banana'] . '' . $row['orange'] . '' . $row['cake'] . '' . $row['grapes'] . '' . $row['egg'] . '
';}else {echo "exec errormsg: ".db2_stmt_errormsg($stmt);}db2_close($conn);?>'; print_r($val); echo '
'; } ?>
Stored Procedure not being called with Groovy in Ready API

I am trying to call a stored procedure through groovy in ReadyAPI. I am doing it this way, because the built in JDBC request does not seem to handle INOUT parameters.

The call seems like it works, but it doesn't actually make the call from what I can tell. One of the parameters is an INOUT parameter and after the call, I just get back what I passed in. There is supposed to be a conversion of the value passed. Also, there is some database logging that should happen that is not. However, the 2 OUT parameters are returned as expected. If I take the same call from the script and run it in a database tool, everything works as expected, so there is not an issue with the stored proc itself.

Here is the code from groovy script:

import groovy.sql.Sqldef globalUser = context.expand('${#Global#Username}')def globalPassword = context.expand('${#Global#Password}')def sql = Sql.newInstance("jdbc:as400://server/library", globalUser, globalPassword, "com.ibm.as400.access.AS400JDBCDriver")sql.call("{call sp_storedproc('inparm1', 'inparm2', 'inparm3', 'inparm4', ${Sql.inout(Sql.VARCHAR('inout5'))}, ${Sql.VARCHAR},${Sql.VARCHAR})}") { parm5, parm6, parm7->log.info parm5log.info parm6log.info parm7}

I have also tried calling the stored procedure like this, but I always get data type mismatch errors. I don't know what the real difference is.

sql.call '{call storedproc(?,?,?,?,?,?,?)}', ["inparm1", "inparm2", "inparm3", "inparm4", "${Sql.inout(Sql.VARCHAR('inout5'))}", "${Sql.VARCHAR}", "${Sql.VARCHAR}"]

Error message for Data Mismatch:

Wed Jun 21 08:46:01 EDT 2017:ERROR:java.sql.SQLException: Data type mismatch.java.sql.SQLException: Data type mismatch. at com.ibm.as400.access.JDError.throwSQLException(JDError.java:385) at com.ibm.as400.access.SQLChar.set(SQLChar.java:152) at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:3173) at com.ibm.as400.access.AS400JDBCPreparedStatement.setObject(AS400JDBCPreparedStatement.java:2640) at groovy.sql.Sql.setObject(Sql.java:4137) at groovy.sql.Sql.setParameters(Sql.java:4102) at groovy.sql.Sql.call(Sql.java:3007) at groovy.sql.Sql$call$3.call(Unknown Source) at Script13.run(Script13.groovy:28) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:92) at com.eviware.soapui.support.scripting.groovy.SoapUIProGroovyScriptEngineFactory$SoapUIProGroovyScriptEngine.run(SoapUIProGroovyScriptEngineFactory.java:79) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:156) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:274) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)

Thanks in advance for any help.

implementation of combining graph-based queries and relational-based queries [on hold]

I am looking for some advice on how to combine graph and relational databases, specifically how to implement and bring together both styles of queries in one script.

Typically we use DB2 to query very large healthcare relational datasets, but we are having to deal with in the near future SNOMED CT, a coding schema that is essentially built with graph-based datasets in mind (i.e. LIKE statements will be rendered useless and subsetting data will depend upon relationships).

I am reading up on NOSQL, Neo4j and Cypher queries which is recommended by thos adopting SNOMED CT, but I have not come across anything about how to incorporate using graph based data whilst still maintaining relational databases.

Ideally I would like to be able to call on NOSQL/Cypher queries within standard SQL queries. I'd imagine this would be implemented via some wrapper code (Python, R?).

Does anyone have any experience of having to handle both relational and graph based data?

Thanks.

For Python-remote AS400 DB2 connection, do we have to use proprietary IBM ODBC driver?

I have a very concise question and i hope it is not put on hold : To connect from Python on Windows to a remote AS400 DB2, over any one of the Python DB API interfaces (ibm_db, pyodbc, pyDB2 or any other one), do we in any case have to use IBMs proprietary ODBC driver? In other words, is there any non-proprietary way of connecting to a remote iseries? Thank you in advance..

Using db2 10.5 exception while trying to connect to db

Hi I am unable to connect to database from cmd, there is a problem in establishing a connection. I am using DB2 10.5 version client. Databse is on 10.5 server (Triend with db on 9.7 version server too).

I get the below excpetion :

com.ibm.db2.jcc.am.SqlException: [jcc][4038][12241][3.69.24] T2LUW exception: SQL1042C An unexpected system error occurred. SQLSTATE=58004 ERRORCODE=-1042, SQLSTATE=58004 at com.ibm.db2.jcc.am.gd.a(Unknown Source) at com.ibm.db2.jcc.am.gd.a(Unknown Source) at com.ibm.db2.jcc.am.gd.a(Unknown Source) at com.ibm.db2.jcc.uw.UWExceptionGenerator.a(Unknown Source) at com.ibm.db2.jcc.uw.UWExceptionGenerator.a(Unknown Source) at com.ibm.db2.jcc.uw.UWConnection.a(Unknown Source) at com.ibm.db2.jcc.uw.f.b(Unknown Source) at com.ibm.db2.jcc.uw.UWConnection.a(Unknown Source) at com.ibm.db2.jcc.uw.UWConnection.a(Unknown Source) at com.ibm.db2.jcc.uw.UWConnection.(Unknown Source) at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source) at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(Unknown Source) at com.ibm.db2.jcc.DB2Driver.connect(Unknown Source) at com.ibm.db2.jcc.DB2Driver.connect(Unknown Source) at java.sql.DriverManager.getConnection(DriverManager.java:512) at java.sql.DriverManager.getConnection(DriverManager.java:171) at DBOperation.setConnection(DBOperation.java:38) at DBOperation.(DBOperation.java:11) at VTLoadStandard.main(VTLoadStandard.java:237)

Please help on resolving the issue.

DB2ResultSet.Read(). ERROR [24000] [IBM] CLI0115E Invalid cursor state. SQLSTATE=24000

We have problem with the application run DB2ResultSet.Read(). Sometime will get ERROR :

[24000] [IBM] CLI0115E Invalid cursor state. SQLSTATE=24000.

Database : DB2 for Linux, UNIX and Windows V10.5Client: Windows 7 64bit

Method:

 public int EventGetEvSegmentCnt(string SegmentID, string strEvntGroup) { int strGroupCnt = 0; string strSQL = string.Empty;`enter code here` DB2ResultSet objRs; if (string.IsNullOrEmpty(SegmentID) || string.IsNullOrEmpty(strEvntGroup)) { strGroupCnt = 0; } else { strSQL = " SELECT COUNT(EVNT_CODE) AS EVNT_GROUP_COUNT FROM E_SEGMENT_EVENT WHERE C_SEGMENT_ID = " + SegmentID + " AND EVNT_GROUP = " + strEvntGroup; Common.DatabaseHelper helper = new Common.DatabaseHelper(); objRs = helper.ExecuteResultSet(strSQL); if (objRs.Read()) { strGroupCnt = 0; } else { strGroupCnt = int.Parse(objRs["EVNT_GROUP_COUNT"].ToString()); } } return strGroupCnt; }

Error Message:

[Information] System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> IBM.Data.DB2.DB2Exception (0x80004005): ERROR [24000] [IBM] CLI0115E Invalid cursor state. SQLSTATE=24000 at IBM.Data.DB2.DB2DataBuffer.FetchScroll(FetchType fetchType, Int64 offset, Int32 numRows) at IBM.Data.DB2.DB2DataBuffer.FetchNext() at IBM.Data.DB2.DB2DataReader.Fetch(FetchDirection direction, Int64 offset, Boolean& isDeleted) at IBM.Data.DB2.DB2ResultSet.Read()

Please help.

DatabaseHelper:

public class DatabaseHelper { public DatabaseHelper() { } public DataSet ExecuteDataSet(string commandText, List parameters = null) { var command = GetCommand(commandText, parameters); var adapter = new DB2DataAdapter(); adapter.SelectCommand = command; var ds = new DataSet(); adapter.Fill(ds); adapter.Dispose(); return ds; } public DB2DataReader ExecuteReader(string commandText, List parameters = null) { var command = GetCommand(commandText, parameters); return command.ExecuteReader(CommandBehavior.CloseConnection); } public DB2ResultSet ExecuteResultSet(string commandText, List parameters = null) { var command = GetCommand(commandText, parameters); //DB2ResultSet result = command.ExecuteResultSet(CommandBehavior.Default, DB2CursorType.Dynamic); DB2ResultSet result = command.ExecuteResultSet(CommandBehavior.CloseConnection, DB2CursorType.Dynamic); return result; } public DB2ResultSet ExecuteResultSetStatic(string commandText, List parameters = null) { var command = GetCommand(commandText, parameters); // DB2ResultSet result = command.ExecuteResultSet(CommandBehavior.Default, DB2CursorType.Static); DB2ResultSet result = command.ExecuteResultSet(CommandBehavior.CloseConnection, DB2CursorType.Static); return result; } public int ExecuteNonQuery(string commandText, List parameters = null) { var command = GetCommand(commandText, parameters); int result = command.ExecuteNonQuery(); command.Connection.Close(); return result; } public void ExecuteSQLArray(string[] arrSQL) { var command = new DB2Command(); command.Connection = GetConnection(); command.CommandType = CommandType.Text; command.CommandTimeout = 600; command.Transaction = command.Connection.BeginTransaction(); try { foreach (string strSQL in arrSQL) { if (!string.IsNullOrEmpty(strSQL)) { command.CommandText = strSQL; command.ExecuteNonQuery(); } } command.Transaction.Commit(); } catch (Exception) { command.Transaction.Rollback(); throw; } command.Connection.Close(); command.Dispose(); } public DB2Connection GetConnection() { var conn = new DB2Connection(System.Configuration.ConfigurationManager.ConnectionStrings["DB2_Conn"].ConnectionString); conn.Open(); return conn; } public DB2Command GetCommand(string commandText, List parameters) { var command = new DB2Command(commandText); command.Connection = GetConnection(); command.CommandTimeout = 600; if (parameters != null) { foreach (var parameter in parameters) { command.Parameters.Add(parameter); } } return command; } }