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.

 

 
db2RemStgManager analyse error based on error code

I am using a shell script to put a file in object storage. For this I am using;

db2RemStgManager S3 put

For now I am deliberately providing wrong creds.

I want to log a different error message if authentication failed. Currenlty when I log what the above command returns I see:

Error code: 251Error message:Recovery mode detected. Local diag path = |/tmp/a|. szICCPath = |/opt/ibm/db2/V11.1.0.0/lib64|.

From what I understand is 251 error code signifies shell's writing error. How can check for authentication errors? when I look at /tmp/a/db2diag.log I can't see any specific authentication error message.

qsh: 001-0078 Process ended by signal 5 when using stmt.bindParam()

Whenever I attempt to run the following code with the statement.bindParam(..) I receive the qsh: 001-0078 Process ended by signal 5 error. There is not much documentation on the error other than possibly not having permissions.

Here is the link to an explanation of the error:

http://www-01.ibm.com/support/docview.wss?uid=swg21690175

I don't understand why it ONLY happens when the statement.bindParam() is in there. I can get it to run fine otherwise. Sadly, I need the bindParam and I need prepared statements here.

Any ideas?

const db = require('/QOpenSys/QIBM/ProdData/OPS/Node4/os400/db2i/lib/db2a');const sql = 'CALL QXMLSERV.iPLUG512K(?,?,?,?)';const connection = new db.dbconn();connection.conn('*LOCAL');const statement = new db.dbstmt(connection);const ipc = '*NA';const ctl = '*here';const xmlIn = `system 'wrksbs'`;const xmlOut = '';// note 4 paramsconst params = [[ipc], [ctl], [xmlIn], [xmlOut],];statement.prepare(sql, (error) => { if (error) { throw error; } statement.bindParam(params, (error) => { if (error) { throw error; } statement.execute((out, error) => { if (error) { throw error; } console.log('OUT:', out); statement.close(); }); });});

}

Correct My DB2 Query that I am Getting Error

I am using below DB2 query to get the Specific Length of Characterfrom CLASSIFICATION Coulmn with Condition 0 before the '\', but I am getting error.

select SERVICE_REQUEST,(case when (SUBSTR(CLASSIFICATION,LOCATE('\',CLASSIFICATION)-2,1))='0' then RIGHT(CLASSIFICATION,LENGTH(CLASSIFICATION)-LOCATE('\',CLASSIFICATION)) ELSE CLASSIFICATIONend) as CLASSIFICATION2 from REPORTDB3.MAXIMO_INDIA_SR_MONTHLY_REPORT where length(SERVICE_REQUEST)=7

ERROR:

An error occurred while processing the results. - The statement was not executed because a numeric argument of a scalar function is out of range.. SQLCODE=-138, SQLSTATE=22011, DRIVER=4.19.56

Can you please Help me on this.

Regards,Sambit

Already Gone to Stackoverflow for the answers

Getting error while configuring db2 database in ibm pim (ISPM)

We have setup IBM privileged identity manager(ISPM) and db2 instance has also been started. However while setting up standalone node and adding database configuration, we go below error.

0000001f com.ibm.identity.utils.IdentityLogger I Identity tables does not exist in the database.

0000001f com.ibm.identity.utils.IdentityLogger I Executing Mesa commands for Database

0000001f com.ibm.identity.utils.IdentityLogger E Error while executing command : [Ljava.lang.String;@98b7797b

Can you please help with this?

How does the DB2 .prepare() function work within Node?

I am attempting to setup prepared insert statements but I am having difficulty finding documentation on using DB2i with Node.

I found a website that shows how I would assume the function would work..

var db = require('/QOpenSys/QIBM/ProdData/OPS/Node4/os400/db2i/lib/db2');var data = {};db.init();db.conn(config.db_name);db.prepare(query, function(err, stmt) { if(err) { console.log(err); } console.log(stmt); }

The issue is, stmt comes back as undefined and there is no error. I am very confused on how I am supposed to setup this function and I can't find documentation anywhere.

I have also done a

console.log(db.prepare.toString());

It shows a function exists.

Number of host variables not valid? I have one ? and one variable being used
module.exports.createMessage = function(query, obj, callback) { if (config.db_name === '*LOCAL') { const db = require('/QOpenSys/QIBM/ProdData/OPS/Node4/os400/db2i/lib/db2a'); var dbconn = new db.dbconn(); dbconn.conn("*LOCAL"); var sqlQuery = "SELECT * FROM SCST.CLIENT WHERE CLMRECID = ?"; var params = [obj.content]; var stmt = new db.dbstmt(dbconn); stmt.prepare(sqlQuery, function() { stmt.bindParam([params], function(err) { if(err) { console.log(err); } stmt.execute(function(out, err) { if(err) { console.log(err); } console.log('Output Params: ', out); stmt.close(); }) }); }); }};

I keep receiving the error that the "Number of host variables is not valid".

I am reading that this means the amounts do not match up. I clearly have one ? and one variable being used to fill that. I have tried many things and have had no success.

Any ideas?

Edit: I have updated my post and gotten rid of the issue. Only problem is now I get the following...

 qsh: 001-0078 Process ended by signal 5. 
How to assign a value of 0 if a row doesn't meet the requirements

I'm trying to return an actor name field (A_Name) and the movie role field (MR_Role) with the number of times each actor has an "Uncredited" in their role. I also want to return a 0 if that specific actor does not have an "Uncredited" in their role. You'll see in my table formats there's another table, but I don't need to use it to run this query since that just has movie id and year of release.

I've used this code to return the amount of uncredited roles for each actor, but without the 0's:

SELECT A_NAME, COUNT(MR_ROLE) FROM ACTOR JOIN MOVIEROLE ON ACTOR.A_ID = MOVIEROLE.A_ID WHERE MOVIEROLE.MR_ROLE LIKE '%UNCREDITED%'GROUP BY A_NAME;

Here are the results from the above query

These are the formats of the tables I'm using:

ACTOR TABLE: (A_ID INTEGER NOT NULL, A_NAME CHAR(25) NOT NULL, A_YOB INTEGER NOT NULL,)MOVIEROLE TABLE:(MR_ID INTEGER NOT NULL, A_ID INTEGER NOT NULL, M_ID INTEGER NOT NULL, MR_ROLE CHAR(45), PRIMARY KEY (MR_ID), FOREIGN KEY(A_ID) REFERENCES ACTOR, FOREIGN KEY(M_ID) REFERENCES MOVIE)
Is there a way to interrogate the status of AS400 jobs from VBA?

I'm trying to provide a user interface in Access to display the status of scheduled AS400 jobs, to show them as either pending, running, or complete.

I've inherited some code which schedules several jobs, which runs fine - but there's no way the user can see what the job status is, and they sometimes hang.

Completion is currently signaled via a numeric counter held in the tables which the jobs update, and there's a Form Timer event which regularly checks for completion.

Using this method, the code can tell when processing on the AS400 is complete, but not if it's hung or failed, or if the server is heavily loaded and running slowly.

I've tried to find code examples on the internet but drew a blank. All I could find that helped was the IBM Database Manual (pdf and online) and a pdf slideshow entitled "iSeries Access ActiveX Development" by Troy C Bleeker of IBM.

Using these resources, I've written some code using the IBM i Access for Windows ActiveX Object Library (cwbx) to talk to the AS400 which works fine, and I can issue the WRKUSRJOB command to check job statuses, but I can't figure out how (or even if) it's possible to view the results of the command.

Is there a way of doing this, and if so, how?

Dim SysNames As New cwbx.SystemNamesDim SvrName As StringDim Svr As New cwbx.AS400SystemDim Svc As cwbx.cwbcoServiceEnumDim Cmd As New cwbx.CommandDim x As New cwbx.DataQueue'Set server nameSvrName = SysNames.DefaultSystemDebug.Print SvrNameSvr.Define SvrName'Set service typeSvc = cwbcoServiceRemoteCmd'Connect and testSvr.Connect SvcDebug.Print Svr.IsConnected(Svc)'CommandSet Cmd.System = SvrDebug.Print "WRKUSRJOB"Cmd.Run "WRKUSRJOB"```'How to get results of the command? Nothing in ErrorsDebug.Print SprintF("Errors: %s", Cmd.Errors.Count)```'Tidy upSvr.Disconnect Svc'Object destructionSet Cmd = NothingSet Svr = NothingSet SysNames = Nothing

It would be great if eg the Errors collection contained the results of the command, or if they could be piped or redirected to a file or queue that could be read by the ActiveX, but I keep drawing a blank.

How to store the select result to a new column from the same table?

I am using DB2 of IBM. I have a table stockdb(date, close)I am calculating the 30 day moving average of it and store it into the same table.

alter stockdb add ma as avg("close") over(order by "date" rows between 29 preceding and current row)from stockdb;
DB2 with .NET Core 2.1

I installed IBM.Data.DB2.Core Version (1.2.2.100) with Visual Studio 2017 & .Net Core 2.1. I was trying to test simple DB2 (z/OS server) connection and getting the below error. Our DB2 Server type is OS390 and version is 11.

ERROR [42968] [IBM] SQL1598N An attempt to connect to the database server failed because of a licensing problem.

 using (DB2Connection con = new DB2Connection("Server=xxxx.xxxx.com:446;Database=XXXX;UID=XXXXXX;PWD=xxxxx")) { try { con.Open(); } catch (Exception ex) { throw ex; } } 

Also I copied the license file to .nuget\packages\ibm.data.db2.core\1.2.2.100\build\clidriver\license folder. I tried everything mentioned here: https://www.ibm.com/developerworks/community/blogs/96960515-2ea1-4391-8170-b0515d08e4da/entry/Instructions_for_downloading_and_using_DB2_NET_Core_provider_package?lang=en

Any thoughts?