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 SQL convert decimal to character pad with zeros

How can I convert DB2 DECIMAL(11) from 12345678 to character value 00012345678

db2 hit Reason: Password expired. ERRORCODE=-4214, SQLSTATE=28000 even password is correct

Good day,

I am running a maven program, and it involve connection to db. Here is part of my code of the database config:

 @Bean DataSource dataSource() { DriverManagerDataSource ds = new DriverManagerDataSource(); ds.setUrl(environment.getProperty(PROPERTY_URL)); ds.setUsername(environment.getProperty(PROPERTY_USERNAME)); ds.setPassword(environment.getProperty(PROPERTY_PASSWORD)); ds.setDriverClassName(environment.getProperty(PROPERTY_DRIVER)); return ds; }

I can login successful with the username and password, however, my program keep hitting the following error:

18:13:06,227 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-1) SQL Error: -4214, SQLState: 2800018:13:06,227 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-1) [jcc][t4][2012][11248][4.19.49] Connection authorization failure occurred. Reason: Password expired. ERRORCODE=-4214, SQLSTATE=2800018:13:06,232 ERROR [io.undertow.request] (default task-1) UT005023: Exception handling request to /springData/person2: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Could not open connection at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) at javax.servlet.http.HttpServlet.service(HttpServlet.java:687) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:85) at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62) at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36) at org.wildfly.extension.undertow.security.SecurityContextAssociationHandler.handleRequest(SecurityContextAssociationHandler.java:78) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131) at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46) at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64) at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60) at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77) at io.undertow.security.handlers.NotificationReceiverHandler.handleRequest(NotificationReceiverHandler.java:50) at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler.handleRequest(JACCContextIdHandler.java:61) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at org.wildfly.extension.undertow.deployment.GlobalRequestControllerHandler.handleRequest(GlobalRequestControllerHandler.java:68) at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:292) at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:81) at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:138) at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135) at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48) at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43) at org.wildfly.extension.undertow.security.SecurityContextThreadSetupAction.lambda$create$0(SecurityContextThreadSetupAction.java:105) at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1508) at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1508) at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1508) at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1508) at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1508) at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:272) at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:81) at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:104) at io.undertow.server.Connectors.executeRootHandler(Connectors.java:326) at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:812) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Could not open connection at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:427) at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371) at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:329) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) at com.journaldev.spring.services.PersonService$$EnhancerBySpringCGLIB$$4f74dce4.getAllPersons() at com.journaldev.spring.controller.PersonController2.getAll2(PersonController2.java:26) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ... 43 moreCaused by: javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Could not open connection at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310) at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1397) at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:62) at org.springframework.orm.jpa.DefaultJpaDialect.beginTransaction(DefaultJpaDialect.java:70) at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:377) ... 63 moreCaused by: org.hibernate.exception.JDBCConnectionException: Could not open connection at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:67) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:221) at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1426) at org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:59) ... 65 moreCaused by: com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: [jcc][t4][2012][11248][4.19.49] Connection authorization failure occurred. Reason: Password expired. ERRORCODE=-4214, SQLSTATE=28000 at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.t4.b.f(Unknown Source) at com.ibm.db2.jcc.t4.b.b(Unknown Source) at com.ibm.db2.jcc.t4.z.r(Unknown Source) at com.ibm.db2.jcc.t4.z.k(Unknown Source) at com.ibm.db2.jcc.t4.z.c(Unknown Source) at com.ibm.db2.jcc.t4.b.k(Unknown Source) at com.ibm.db2.jcc.t4.b.b(Unknown Source) at com.ibm.db2.jcc.t4.b.a(Unknown Source) at com.ibm.db2.jcc.t4.b.b(Unknown Source) at com.ibm.db2.jcc.t4.b.a(Unknown Source) at com.ibm.db2.jcc.t4.b.a(Unknown Source) at com.ibm.db2.jcc.t4.b.a(Unknown Source) at com.ibm.db2.jcc.t4.b.(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:664) at java.sql.DriverManager.getConnection(DriverManager.java:208) at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173) at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164) at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:153) at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119) at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:70) at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292) at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214) ... 70 more

I also just change the db2 user password, also hit back this same error. I believe there is something wrong with my configuration, kindly advise.

SYSSEQUENCES in SYSIBM type *FILE not found Error

In my spring boot application i'm calling for AS400 DB according to following application properties

server.port=8082spring.jpa.database=default#Config for Oracle-DCR application DBspring.datasource.url= jdbc:as400:[ip]/[DB Name]spring.datasource.username=usernamespring.datasource.password=passwordspring.datasource.driver-class-name=com.ibm.as400.access.AS400JDBCDriver#hibernate configspring.jpa.database-platform=org.hibernate.dialect.DB2400Dialectspring.jpa.show-sql=truespring.jpa.hibernate.ddl-auto=updatespring.jpa.generate-ddl=truespring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImplspring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate4.SpringSessionContext

But when i'm running my application I'm getting following error.

Exception in thread "main" org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [lk/pb/mediator/persistence/util/HibernateDataSource.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1080) at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:857) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:543) at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122) at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:693) at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:360) at org.springframework.boot.SpringApplication.run(SpringApplication.java:303) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1118) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1107) at lk.pb.mediator.web.boot.PBmediator.main(PBmediator.java:27)Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:954) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:882) at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:360) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:382) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:371) at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:336) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) ... 16 moreCaused by: org.hibernate.exception.SQLGrammarException: Error creating DatabaseInformation for schema migration at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:127) at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:101) at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:472) at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:444) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:879) ... 23 moreCaused by: java.sql.SQLException: [SQL0204] SYSSEQUENCES in SYSIBM type *FILE not found. at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650) at com.ibm.as400.access.JDError.throwSQLException(JDError.java:621) at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1557) at com.ibm.as400.access.AS400JDBCStatement.executeQuery(AS400JDBCStatement.java:2115) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy76.executeQuery(Unknown Source) at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:42) at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:64) at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.(DatabaseInformationImpl.java:60) at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:123) ... 27 more

What is wrong with my application? Is there something wrong in my application properties. In here i cannot do any changes from DB side. I have to work with already implemented DB. I cannot recreate DB from my jpa files. I have seen someone resolved this problem by changing following property

spring.jpa.hibernate.ddl-auto=create

but in my scenario i cannot do it. please give me another solution.

Can't order query correctly

A while ago I requested help to code a LEFT JOIN filtering in a particular way that the result postition the desired value in the first row.

Need to retrieve table's last inserted/updated record with some exclusions

The thing now is that there are many cases which are mixing data. The scenario is that on the same table we have 2 values that we need to organize on different columns. The PO_ID is unique, but can have 1 or more values on the other tables, and for this particular case 1 PO_ID has 3 SHIP_ID_CUS values. We only need 1 PO_ID per row (no duplicates) that is way we used the MAX() and GROUP BY.

Here is a piece of the code that I think cause issues.

selectz.po_id,max(scdc.ship_id) as ship_id_cdc,max(lscdc.ship_evnt_cd) as last_event_cdc,max(lscdc.ship_evnt_tms) as event_tms_cdc,max(scus.SHIP_ID) as ship_id_cus, max(lscus.ship_evnt_cd) as last_event_cus,max(lscus.ship_evnt_tms) as event_tms_cusfrom TABLE.A zleft join (select distinct po_id, iltc.ship_id, s.ship_to_loc_code from TABLE.B iltc inner join TABLE.C s on iltc.ship_id=s.ship_id and iltc.ship_to_loc_code=s.ship_to_loc_code and s.ship_to_ctry<>' ') AS A ON z.po_id = a.po_idleft JOIN TABLE.C scus ON A.SHIP_ID = scus.SHIP_ID AND A.SHIP_TO_LOC_CODE = scus.SHIP_TO_LOC_CODE and scus.loc_type = 'CUS' AND DAYS(scus.shipment_tms)+10 >= DAYS(z.ship_tms)left JOIN TABLE.C scdc ON A.SHIP_ID = scdc.SHIP_ID AND A.SHIP_TO_LOC_CODE = scdc.SHIP_TO_LOC_CODE and scdc.loc_type = 'CDC' AND DAYS(scdc.shipment_tms)+10 >= DAYS(z.ship_tms)left join ( select ship_id_856, ship_to_loc_cd856, ship_evnt_cd, ship_evnt_tms, carr_tracking_num, event_srv_lvl , row_number() over(partition by ship_id order by updt_job_tms desc) as RN FROM TABLE.D WHERE LEFT(ship_evnt_cd, 1) <> '9') lscusON lscus.ship_id_856=scus.ship_id and scus.ship_to_loc_code=lscus.ship_to_loc_cd856 and lscus.rn = 1left join ( select ship_id_856, ship_to_loc_cd856, ship_evnt_cd, ship_evnt_tms, carr_tracking_num, event_srv_lvl , row_number() over(partition by ship_id order by updt_job_tms desc) as RN FROM TABLE.D WHERE LEFT(ship_evnt_cd, 1) <> '9') lscdcON lscdc.ship_id_856=scdc.ship_id and lscdc.ship_to_loc_cd856=scdc.ship_to_loc_code and lscdc.rn = 1WHEREz.po_id = 'T1DLDC'GROUP BY z.po_id 

By searching that condition we get the following result

enter image description here

The problem is that if we search directly on the TABLE.D, the last event that we need (with last update record tms) is another one (X1) and somehow the date is incorrect.

enter image description here

What is even more weird, is that if we search for the ship_id_cus on the original query, we get the correct code but still with a wrong date...

WHERE--z.po_id = 'T1DLDC'scus.ship_id = 'D30980'GROUP BY z.po_id 

enter image description here

I tried other logic changes like modifying the left joins to search on a subquery.

left JOIN ( select * from TABLE.C order by updt_job_tms desc) scus ON A.SHIP_ID = scus.SHIP_ID AND A.SHIP_TO_LOC_CODE = scus.SHIP_TO_LOC_CODE and scus.loc_type = 'CUS' AND DAYS(scus.shipment_tms)+10 >= DAYS(z.ship_tms)

But this is also giving the same exact results by searching either by po_id or ship_id_cus

Any ideas or comment will be much appreciated.

Thanks

------------------------------------UPDATE-----------------------------------

Adding the result of the LEFT JOIN with the row_partition() including all the ship_id_cus for that po_id, and all the codes with the tms. None match here.

enter image description here

Based on all these, it should be the last ship_id_cus with X1 event/tms. If we exclude also the ones starting with 9, we would get the following result.

enter image description here

(I am not applying here ordering by ship_id_cus, which already described before that did not work either the way I implemented)

how to add local jar to maven project

I trying a small maven project, and I try to connect to db2 database, so I need the com.ibm.db2.jcc.DB2Driver jar for the connection.

I tried many method from internet but still fail, here is some of the method I tried:

  1. edit in pom.xml

      com.ibm.db2 db2jcc4 10.5.0.7 system  /home/Workspace/book/springData/lib/db2jcc4-10.5.0.7.jar  
  2. use maven command to install:

    mvn install:install-file -Dfile=/home/Workspace/book/springData/lib/db2jcc4-10.5.0.7.jar -DpomFile=/home/Workspace/book/springData/pom.xml

    or

    mvn install:install-file -Dfile=/home/meow/Workspace/mbsb-cib/springData/lib/db2jcc4-10.5.0.7.jar -DgroupId=com.ibm.db2 -DartifactId=db2jcc4 -Dversion=10.5.0.7 -Dpackaging=jar -DgeneratePom=true

But, I am still hitting could not load JDBC driver.

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'dataSource' threw exception; nested exception is java.lang.IllegalStateException: Could not load JDBC driver class [com.ibm.db2.jcc.DB2Driver] at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189) at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588) ... 70 moreCaused by: java.lang.IllegalStateException: Could not load JDBC driver class [com.ibm.db2.jcc.DB2Driver] at org.springframework.jdbc.datasource.DriverManagerDataSource.setDriverClassName(DriverManagerDataSource.java:150) at com.journaldev.spring.config.DataConfig.dataSource(DataConfig.java:52) at com.journaldev.spring.config.DataConfig$$EnhancerBySpringCGLIB$$46013fb.CGLIB$dataSource$0() at com.journaldev.spring.config.DataConfig$$EnhancerBySpringCGLIB$$46013fb$$FastClassBySpringCGLIB$$8fec3ead.invoke() at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228) at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:356) at com.journaldev.spring.config.DataConfig$$EnhancerBySpringCGLIB$$46013fb.dataSource() at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:162) ... 71 moreCaused by: java.lang.ClassNotFoundException: com.ibm.db2.jcc.DB2Driver from [Module "deployment.springData.war" from Service Module Loader] at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:198) at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:412) at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:400) at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:116) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:348) at org.springframework.jdbc.datasource.DriverManagerDataSource.setDriverClassName(DriverManagerDataSource.java:147) ... 82 more
Extract resultset from stored procedure, in Java

I am executing a stored procedure in Java using the old school way:

CallableStatement cs = con.prepareCall("schema.procedure("?,?,?,?");cs.setString(1, "f");cs.setString(2, "f");cs.registerOutParameter(3, Types.CHAR);cs.registerOutParameter(4, Types.CHAR);cs.execute();System.out.println(cs.getString(3));

I can retrieve the output columns, but the procedure also returns other results as part of cursor, which i can retrieve using :

ResultSet rs= cs.executeQuery()while (rs.next()) {for(int i=1;i<=rs.getMetaData().getColumnCount();i++){rs.getString(i)

My question is, can i retrieve both output and the cursor in the same statement ?Thanks,

DB2 connectivity from .NEt core 2.1

I'm trying to connect to a DB2 database from .NET core application. My code needs to run in PCF in Linux stack. However the code development and Jenkins build happen on Windows server. My problem is that for DB2 to work properly with .NET core we need to add separate reference for windows and separate reference in Linux. For windows it will be

and for Linux it will be

Now the question is how will I change the DB2 reference during the build time from windows to Linux?Also I have integration test that needs to be run after the build is completed. Since the Jenkins server is windows, post build, I need to have DB2 windows reference library and just before deployment I need to change it to Linux so that it can be deployed in Linux stack ?

How to join two tables without repeating the same rows for different values in table

First table:

+ ---+-----------+| id | country |+ ---+-----------+| 1 | India || 2 | Australia || 3 | Canada || 4 | France || 5 | Russia |+ ---+-----------+

Second table:

+ ---+-------+------------+| id | user | country_id |+ ---+-------+------------+| 1 | Ojas | 1 || 2 | Raj | 1 || 3 | John | 3 || 4 | Robin | 2 || 5 | Mary | 5 || 6 | Kamal | 4 || 7 | Bipin | 5 || 8 | Rohit | 1 |+ ---+-------+------------+

Expected result:

+ -----------+---------------+-------+| country_id | country | user |+ -----------+---------------+-------+| 1 | India | Ojas || null | null | Raj || null | null | Rohit || 2 | Australia | Robin || 3 | Canada | John || 4 | France | Kamal || 5 | Russia | Mary || null | null | Bipin |+ -----------+---------------+-------+
Which Data Structure is appropriate for this scenario? [on hold]

Need to do this using C#.

Scenario:There are two tables (which we cannot join) from two different databases.

table1 has ID and STATUS columns. table2 also has ID and STATUS columns but with different STATUS values also this table has latest status values.

I have to select records from table2 with latest status values based on IDs from table1 and update the table1 with the selected latest status.

Here I have to maintain lists for IDs and STATUS values from two tables and need to do comparisons and update table1 records.

The records are in thousands. So how do we do it efficiently in C#?

Add SSL Server Certificate for DB2 in JDBC code

I am trying to access a DB2 instance via SSL port.I have the certificate that I need to access it.But I am not sure of the parameter that I need to add it to so that connection is successful.

Dataset data=session.read().jdbc("jdbc:db2://url:port/DBNAME"+":sslConnection=true" , "db2inst1.JOB_STATUS", connectionProperties); connectionProperties.put("driver", "com.ibm.db2.jcc.DB2Driver"); connectionProperties.put("url", "jdbc:db2://url:port/DBNAME"); connectionProperties.put("user", "db2inst1"); connectionProperties.put("password", "xxxxx");

The python code to connect to the same DB2 instance is this :

"DATABASE=DB_NAME;HOSTNAME=url;SSL=TRUE;PORT=port;PROTOCOL=TCPIP;UID=xxxx;PWD=xxxx;SSLServerCertificate=DB2Certificate.arm;;SECURITY=SSL"

I would want to provide the DB2Certificate.arm as a parameter to the jdbc url , similar to the python parameter of SSLServerCertificate . How can I do this ?