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.

 

 
stream.on do not get called when we pass update query in queryStream in ibm_db npm

This is my code :

'use strict';var Pool = require("ibm_db").Pool, pool = new Pool(), cn = process.env.srcConnString console.log("Init")var ret = pool.init(5, cn);const queryBuilder = async (res, query, dbEnv, queryType) => { if (ret != true) { console.log(ret); return false; } else if (queryType === "update") { // if we get a query as update try { pool.open(cn, function (err, conn) { console.log("Using Pooled Connection") var stream = conn.queryStream(query); // the update gets completed here stream.on('data', function (result) { // i want the id in this function but it do not return anything. Doesnt execute even. console.log("result......" + result); conn.close(function () { }); }).once('error', function (err) { // if there is a error this gets executed. I checked. conn.close(); return err; }).once('end', function () { conn.close(); console.log("Connection Closed"); }); }); } catch (e) { conn.close(); console.log("error occurred: " + e); res.status(500).send(e) } } else { // here i execute select querytype. }}module.exports.queryBuilder = queryBuilder;

Trying to return 200 when update is completed which is return in the first if statement when queryType === "update", but once var stream = conn.queryStream(query); is executed, i don't get any id for which the update happened so i can pass it to the calling api.

how to get first and last day of each month for past 4 years from current date in IBM DB2?

I have written the following code to get past 4 years every first and end of month dates from current date in db2.

WITH tempdateseries (StartDate,EndDate) AS (SELECT DATE(1) + (YEAR(CURRENT DATE)-5) YEARS AS StartDate, LAST_DAY(CURRENT DATE - 4 YEARS) AS EndDateFROM sysibm.sysdummy1UNION ALLSELECT StartDate + 1 MONTH, EndDateFROM tempdateseriesWHERE StartDate < LAST_DAY(CURRENT DATE - 1 MONTHS) AND EndDate < LAST_DAY(CURRENT DATE - 1 MONTHS)) SELECT *FROM tempdateseries
How to esablish a Database SSL connection via .ODC file

I have been using .ODC files to get data directly and quickly from a database for years but we have been using a NON-SSL connection : Port number: 50000. We are now forced to use an SSL connection only: Port number: 50001. When I change the port number in the .ODC file I get the error message below.... not mention that I have no idea how to link it to a SLL certificate. Sorry if I mistag this question. I am not sure how to tag it properlly. Also, I an using .ODC files with DB2.

enter image description here

Is SYSDATE officially supported on db2?

It appears that SYSDATE (as in Oracle) works on Db2 even with non-Oracle compliant setting - default, DB2_COMPATIBILITY_VECTOR is not set to ORA.

Only kind of official info I could find is on:https://www.ibm.com/support/pages/node/236545

From DB2 9.7 onwards, SYSDATE has been marked as a keyword which is a special register as synonym for CURRENT TIMESTAMP(0) or CURRENT_TIMESTAMP(0).‚

Background of the question: need my scripts to work both on Oracle and Db2.

Do I need Db2 flavored scripts or can just use SYSDATE for both?

Is there a link to official documentation of the fact?

EDIT:As per comment from @data_henrik

I agree that documentation states

SYSDATE can also be specified as a synonym for CURRENT TIMESTAMP(0).

But this is just CAN. It does not state it IS.

EDIT 2:

It seems I was reading documentation wrong. Thanks @data_henrik.

Spring Boot, Flyway, DB2 - Cannot overwrite Properties

I tried to Update an application to a newer version of Spring Boot 2 and have now a problem during startup that I cannot explain myself.

Here the Log:

2020-05-28 09:23:41.408 INFO 10784 --- [ main] d.v.h.Application : Starting Application on E0088856 with PID 10784 (_\target\classes started by _ in _)2020-05-28 09:23:41.412 INFO 10784 --- [ main] d.v.h.Application : The following profiles are active: serverWARNING: An illegal reflective access operation has occurredWARNING: Illegal reflective access by org.codehaus.groovy.reflection.CachedClass (file:/_/Maven_Repository/org/codehaus/groovy/groovy/2.5.11/groovy-2.5.11.jar) to method java.lang.Object.finalize()WARNING: Please consider reporting this to the maintainers of org.codehaus.groovy.reflection.CachedClassWARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operationsWARNING: All illegal access operations will be denied in a future release2020-05-28 09:23:42.927 INFO 10784 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.2020-05-28 09:23:43.334 INFO 10784 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 401ms. Found 10 JPA repository interfaces.2020-05-28 09:23:44.055 INFO 10784 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.security.access.expression.method.DefaultMethodSecurityExpressionHandler@508ad266' of type [org.springframework.security.access.expression.method.DefaultMethodSecurityExpressionHandler] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)2020-05-28 09:23:44.060 INFO 10784 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'methodSecurityMetadataSource' of type [org.springframework.security.access.method.DelegatingMethodSecurityMetadataSource] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)2020-05-28 09:23:44.455 INFO 10784 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)2020-05-28 09:23:44.464 INFO 10784 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]2020-05-28 09:23:44.464 INFO 10784 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.35]2020-05-28 09:23:44.662 INFO 10784 --- [ main] o.a.c.c.C.[.[localhost].[/hamster] : Initializing Spring embedded WebApplicationContext2020-05-28 09:23:44.662 INFO 10784 --- [ main] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 3076 ms2020-05-28 09:23:44.914 WARN 10784 --- [ main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning2020-05-28 09:23:44.995 INFO 10784 --- [ main] o.f.c.internal.license.VersionPrinter : Flyway Community Edition 6.4.1 by Redgate2020-05-28 09:24:01.370 ERROR 10784 --- [ main] o.s.b.web.embedded.tomcat.TomcatStarter : Error starting Tomcat context. Exception: org.springframework.beans.factory.UnsatisfiedDependencyException. Message: Error creating bean with name 'webSecurityConfig': Unsatisfied dependency expressed through method 'setContentNegotationStrategy' parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.springframework.boot.autoconfigure.web.servlet.WebMvcAutoConfiguration$EnableWebMvcConfiguration': Unsatisfied dependency expressed through method 'setConfigurers' parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'openEntityManagerInViewInterceptorConfigurer' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/JpaBaseConfiguration$JpaWebConfiguration.class]: Unsatisfied dependency expressed through method 'openEntityManagerInViewInterceptorConfigurer' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'openEntityManagerInViewInterceptor' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/JpaBaseConfiguration$JpaWebConfiguration.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.exception.FlywaySqlException:Unable to obtain connection from database (jdbc:db2://CORRECT_URL;retrieveMessagesFromServerOnGetMessage=true;) for user 'CORRECT USER': [jcc][10214][10050][4.26.14] An java.sql.Driver.connect() übergebenes java.util.Properties-Objekt kann von URL nicht überschrieben werden: CORRECT_URL;retrieveMessagesFromServerOnGetMessage=true;. ERRORCODE=-4461, SQLSTATE=42815-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL State : 42815Error Code : -4461Message : [jcc][10214][10050][4.26.14] An java.sql.Driver.connect() übergebenes java.util.Properties-Objekt kann von URL nicht überschrieben werden: CORRECT_URL;retrieveMessagesFromServerOnGetMessage=true;. ERRORCODE=-4461, SQLSTATE=428152020-05-28 09:24:01.390 INFO 10784 --- [ main] o.apache.catalina.core.StandardService : Stopping service [Tomcat]2020-05-28 09:24:01.397 WARN 10784 --- [ main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.context.ApplicationContextException: Unable to start web server; nested exception is org.springframework.boot.web.server.WebServerException: Unable to start embedded Tomcat2020-05-28 09:24:01.410 INFO 10784 --- [ main] ConditionEvaluationReportLoggingListener :Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.2020-05-28 09:24:01.419 ERROR 10784 --- [ main] o.s.boot.SpringApplication : Application run failed

Here my pom.xml:

 org.springframework.boot spring-boot-starter-parent 2.3.0.RELEASE  org.springframework.boot spring-boot-starter   org.springframework.boot spring-boot-starter-web   org.springframework.boot spring-boot-starter-thymeleaf   org.springframework.boot spring-boot-starter-actuator   org.springframework.boot spring-boot-starter-validation   org.springframework.boot spring-boot-starter-jooq   org.springframework.boot spring-boot-configuration-processor   org.springframework.boot spring-boot-starter-data-jpa   com.ibm.db2 jcc   org.flywaydb flyway-core 

And here my application.properties:

# Spring Datasourcespring.datasource.url=@DB_URL@spring.datasource.driverClassName=com.ibm.db2.jcc.DB2Driverspring.datasource.username=@DB_USERNAME@spring.datasource.password=@DB_PASSWORD@# JPA / Hibernatespring.jpa.database-platform=org.hibernate.dialect.DB2Dialectspring.jpa.hibernate.ddl-auto=none# Flywayspring.flyway.enabled=truespring.flyway.table=schema_versionspring.flyway.validate-on-migrate=falsespring.flyway.placeholders.tablespace=@FLYWAY_PLACEHOLDER_TABLESPACE@spring.flyway.placeholders.username=@FLYWAY_PLACEHOLDER_USERNAME@spring.flyway.url=@FLYWAY_DB_URL@spring.flyway.user=@FLYWAY_DB_USERNAME@spring.flyway.password=@FLYWAY_DB_PASSWORD@

@DB_URL@ = @FLYWAY_DB_URL@@DB_USERNAME@ != @FLYWAY_DB_USERNAME@

It seems that the connection cannot been updated for the flyway initializer with the credencials from the application.properties.

I have no clue why. I cannot find the abbreviation from the spring / flyway manual I ran into.

I am getting error while writing query in db2 sql. I need to have a CASE with 3 conditions

Here is my query :

select CASE IF(DATE(Stud40.Start_DT) > '0001-01-01') THEN Stud40.Start_DT ELSE IF(Stud40.Start_DT IS NULL) THEN Stud50.Start_DT ELSE Stud.Start_DT END AS "Stud_joining_DT",

My error is :

SQL Error [42601]: ILLEGAL SYMBOL ">". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.9.78
SQL how to find a multi column maximum in a group?

How can I write an SQL query (DB2) that will run on this table:

| A | B | C | V |+---+---+---+----+| | | | || 1 | 1 | 1 | k1 || | | | || 1 | 1 | 2 | k1 || | | | || 1 | 2 | 3 | k2 || | | | || 2 | 3 | 4 | k2 || | | | || 1 | 2 | 3 | k3 || | | | || 1 | 3 | 5 | k3 || | | | || 1 | 4 | 6 | k3 |+---+---+---+----+

and produce this result

+---+---+---+----+| A | B | C | V |+---+---+---+----+| | | | || 1 | 1 | 2 | k1 || | | | || 2 | 3 | 4 | k2 || | | | || 1 | 4 | 6 | k3 |+---+---+---+----+

that is it will select rows based on a max of a "tuple" (A,B,C) in a group:

or for two rows R1, R2 :

if R1.A <> R2.A return Row where A = Max(R1.A,R2.A)if R2.B <> R2.B return Row where B = Max(R1.B,R2.B)return Row where C = Max(R1.C,R2.C)
DB2 insert performance

I am trying to do a bulk insert into DB/2 from an Excel file.I am running WildFly 12 on my Windows 10 PC. DB/2 is running on a VM under CentOS 7.

Using POI SAX methods, reading the complete XLSX file with 2.5M records takes 80 seconds.

I started off using simple inserts in my EJB program with literals to insert into the table. This took about 1.5 hours.

After reading https://www.idug.org/p/bl/et/blogid=2&blogaid=602 I changed to prepared statements using batches. I also first write to a Global Temporary Table and at the end use "INSERT INTO SELECT ... FROM ".

Turned off auto commit.

The end result is still taking up an hour, or 750 inserts / second.

Prepare the items:

 // get connection using DriverManager class try { connection = DriverManager.getConnection( "jdbc:db2://192.168.0.5:50000/mydb", "hussain", "hussain"); } catch (SQLException e1) { out.println("Failed to get connection"); e1.printStackTrace(); return; } // Clear the table try { Statement statement = connection.createStatement(); statement.execute("TRUNCATE TABLE " + strMainTable + " IMMEDIATE"); statement.execute("ALTER TABLE " + strMainTable + " ALTER COLUMN ID RESTART WITH 1 ACTIVATE NOT LOGGED INITIALLY"); statement.close(); } catch (SQLException e2) { out.println("Failed to clear table
"); e2.printStackTrace(); return; } try { Statement statement = connection.createStatement(); statement.execute("DROP TABLE " + strTempTable); statement.close(); } catch (SQLException e2) { out.println("Failed to delete table
"); } // In DB2: // CREATE USER TEMPORARY TABLESPACE USERTEMP MANAGED BY AUTOMATIC STORAGE // GRANT USE OF TABLESPACE USERTEMP TO USER HUSSAIN try { Statement statement = connection.createStatement(); statement.execute("CREATE GLOBAL TEMPORARY TABLE " + strTempTable + " (" + " COL1 VARCHAR(900 OCTETS), " + " COL2 VARCHAR(20 OCTETS), " + " COL3 VARCHAR(225 OCTETS), " + " COL4 VARCHAR(500 OCTETS)" + " ) NOT LOGGED ON COMMIT PRESERVE ROWS "); statement.close(); } catch (SQLException e2) { out.println("Failed to create table
"); e2.printStackTrace(); } // Step 2.B: Creating JDBC Statement String sql = "INSERT INTO " + strTempTable + " (COL1, COL2, COL3, COL4) VALUES(?,?,?,?)"; try { statementInsert = connection.prepareStatement(sql); connection.setAutoCommit(false); } catch (SQLException e1) { out.println("Failed to prepare statement
"); e1.printStackTrace(); return; }

Then, in the loop, I read the XLSX file and

 try { if(batchRows > 10000) { batchRows = 0; statementInsert.executeBatch(); } //statementInsert.execute(); batchRows++; //lBigBatchRows++; statementInsert.addBatch(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }

At the end:

 Statement statement = connection.createStatement(); statement.execute("INSERT INTO " + strMainTable + " (COL1, COL2, COL3, COL4) " + " SELECT COL1, COL2, COL3, COL4 FROM " + strTempTable); statement.execute("DROP TABLE " + strTempTable); connection.commit();

Yes, DB/2 is running in a VM so write speeds are effected by lots of factors. Still, 750 records / second is extremely slow. The author of the article says his worst speed was 3000/s and best was 140k/s. Would be nice to get close.

What else should I be looking at?

Getting logs from IBM Z/OS

I am new to Mainframe concepts and haven't used any IBM Z-series machines before. But now I need to get db2 logs from the Z/OS platform.

I previously collected logs from IBM AS400 using JT400.jar.(will execute remote command and read the output using java). But for Z/OS, I could not find such things from documentations.

So I need to know the possibilities for the following cases.

  1. Running a remote command and read it's output using any API like JT400.
  2. Forwarding the logs as Syslogs to any SIEM solutions((Like ManageEngine EventLog Analyzer or ArcSight ESM) without any Third-party Installations)
  3. Listing Log files using SSH connection, because I heard that Z/os will have some Unix properties too(for example using JSCH)

Even if there are any other possibilities, please mention below. It would be really helpful. Based on the possibilities only I could consider buying a Z/OS mainframe.

Thanks in advance.

How to connect AS400 with Laravel

For a little while now, I've been looking how to try a connection to AS400 with Laravel 7.

I use this package : https://github.com/cooperl22/laravel-ibmi

I add laravel-ibmi to your composer.json file:

"require": { "cooperl/laravel-ibmi": "^7.0"}

Use composer to install this package.

$ composer update

Run on the command line from the root of my project:

$ php artisan vendor:publish

And set credentials in .env

DB_CONNECTION=ibmiDB_HOST=********DB_PORT=********DB_DATABASE=********DB_USERNAME=********DB_PASSWORD=********

I try a connection with tinket but i have this message :

Psy Shell v0.10.4 (PHP 7.2.24-0ubuntu0.18.04.4 --- cli) by Justin Hileman>>> DB::connection();PDOException with message 'could not find driver

I should set my credentials in app/config/db2.php, not in .env ?