How to check db2 version
You can try the following query: SELECT service_level, fixpack_num FROM TABLE (sysproc.env_get_inst_info()) as INSTANCEINFO It works on LUW, so I can’t guarantee that it’ll work on z/OS, but it’s worth a shot.
db2
Equivalent of LIMIT for DB2
Using FETCH FIRST [n] ROWS ONLY: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.perf/db2z_fetchfirstnrows.htm SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY FROM EMP ORDER BY SALARY DESC FETCH FIRST 20 ROWS ONLY; To get ranges, you’d have to use ROW_NUMBER() (since v5r4) and use that within the WHERE clause: (stolen from here: http://www.justskins.com/forums/db2-select-how-to-123209.html) SELECT code, name, address FROM ( SELECT row_number() OVER ( ORDER … Read more
Spring boot Hibernate error “Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set” when working with multiple data sources
I figure it out. Modify method entityManagerFactory for both Db2Configuration and OracleConfiguration to supply them with the information about hibernate dialect: for DB2 @Primary @Bean(name = “db2EntityManagerFactory”) public LocalContainerEntityManagerFactoryBean entityManagerFactory( EntityManagerFactoryBuilder builder , @Qualifier(“db2DataSource”) DataSource dataSource) { final HashMap<String, Object> hibernateProperties = new HashMap<String, Object>(); hibernateProperties.put(“hibernate.dialect”, “org.hibernate.dialect.DB2390Dialect”); return builder .dataSource(dataSource) .packages(“project.dataconfig.db2.entity”) .properties(hibernateProperties) .persistenceUnit(“db2persistanceunit”) .build(); } … Read more
how to transform comma separated column into multiples rows in db2
You really should not be storing data like this. Fortunately, there is a way to undo the damage with recursive SQL, something along these lines: WITH unpivot (lvl, id, fk_ref, reference, tail) AS ( SELECT 1, id, fk_ref, CASE WHEN LOCATE(‘,’,reference) > 0 THEN TRIM(LEFT(reference, LOCATE(‘,’,reference)-1)) ELSE TRIM(reference) END, CASE WHEN LOCATE(‘,’,reference) > 0 THEN … Read more
INNER JOIN in UPDATE sql for DB2
You don’t say what platform you’re targeting. Referring to tables as files, though, leads me to believe that you’re NOT running DB2 on Linux, UNIX or Windows (LUW). However, if you are on DB2 LUW, see the MERGE statement: update: note that Db2 for IBM i added MERGE support in late 2010 to v7.1 and … Read more
Relationship between catalog, schema, user, and database instance
In Oracle: server instance == database == catalog == all data managed by same execution engine schema == namespace within database, identical to user account user == schema owner == named account, identical to schema, who can connect to database, who owns the schema and use objects possibly in other schemas to identify any object … Read more
An unexpected token “CREATE TRIGGER
You have 2 things going on here – 1) When the “;” character is part of the SQL statement, it’s necessary to use a different character to terminate the statement. I typically use “@”. To tell the “db2” command that you have chosen a different character, use db2 -td@ or if you want to read … Read more
Regular Expressions in DB2 SQL
Starting with DB2 11.1 there is built-in regex support. One of the new function is REGEXP_SUBSTR and there are some more. SELECT REGEXP_SUBSTR(‘hello to you’, ‘.o’,1,1) FROM sysibm.sysdummy1
Does DB2 have an “insert or update” statement?
Yes, DB2 has the MERGE statement, which will do an UPSERT (update or insert). MERGE INTO target_table USING source_table ON match-condition {WHEN [NOT] MATCHED THEN [UPDATE SET …|DELETE|INSERT VALUES ….|SIGNAL …]} [ELSE IGNORE] See: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0010873.html https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en
SQL update from one Table to another based on a ID match IN db2
DB2 does indeed support joins in an UPDATE statement, only not the way you think — DB2 follows the SQL ANSI standard: UPDATE Sales_Import SI SET Sales_Import.AccountNumber = ( SELECT RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE SI.LeadID = RAN.LeadID ) The above assumes that LeadID uniquely identifies records in RetrieveAccountNumber, otherwise you will get an error … Read more