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.

 

 
Multiple records update into a column with inner join DB2

I have written this update statement to update records in DB2.

UPDATE schemaname.tablenameSET fillerinfo = myfillerinfoFROM schemaname.tablename,schemaname.tablename1WHERE ID = uid1AND PID=uuplanidAND ACCTNO=uacctno

However, when running on AS400 DB2.

I have Data like this:

 Table1 | Table2 fillerinfo |myfillerinfo -----------|------------- A | b c | D E | f

Like above i have 500K records.

Its showing error: Statement can not be executed.

I'm getting error:

Keyword FROM not expected. Valid tokens: USE SKIP WAIT WITH WHERE. Cause . . . . . : The keyword FROM was not expected here. A syntax error was detected at keyword FROM. The partial list of valid tokens is USE SKIP WAIT WITH WHERE. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

So I refereed this link to see if it helps: https://oscarvalles.wordpress.com/2013/05/12/db2-update-with-inner-joins/

But, this example in the above link is showing to update single records.

How can I update multiple records in a column with join? Please suggest.

Insert into temp table from IBM DB2 Stored procedure not inserting records in SQL

I am new to IBM DB2 and i am trying to insert into a temp table in SQL with the result set from an IBM DB2 Stored procedure.The stored procedure returns correct data when called from SQL server individually.

But when i create a temp table and try to insert records into the temp table from IBM DB2 SP it says '(0 row(s) affected)'

I am calling the DB2 SP individually like this in SQL server.

EXEC ('{CALL SCHEMA.SP_NAME (''PARAM1'', ''PARAM2'', ''PARAM3'')}') AT SERVERNAME;

This gives correct output.

Now i create a temp table with the same column names and similar definitions as the SP result setand try to insert to the temp table like this

CREATE TABLE #reorder ( PRODCODE VARCHAR(35), BUYNUM VARCHAR(10), DISCO CHAR(1), QTY_AVAIL INT , RECENT_NBR_DAY INT, RECENT_ORD_QTY INT, PER_DAY_DEMAND_QTY DECIMAL(7,2), AVAIL_NBR_DAY DECIMAL(7,2), YEAR_ORD_QTY INT, YEAR_PER_DAY_DEMAND DECIMAL(7,2), PONUM VARCHAR(10), POQTY INT, ISSUEDAT DATETIME, PRODDESC VARCHAR(25), CUCODE VARCHAR(15), LEADTIME INT, DEMANDPERDAY DECIMAL(7,2), ANNUALUSAGE INT, MAXIMUMDAILYUSAGE INT, SAFETYSTOCK DECIMAL(7,2), REPORDERPOINT DECIMAL(7,2), PRCVNR INT, )INSERT INTO #reorder EXEC ('{CALL SCHEMA.SP_NAME (''PARAM1'', ''PARAM2'', ''PARAM3'')}') AT SERVERNAME;

Even though the SP returns records it is not getting inserted into the temp table. It gives (0 row(s) affected) message.

I tried increasing the length of the column data type in the temp table and suddenly it started working, but it stopped working again.

I am unable to figure out what exactly is the issue!! Is there anything that needs to be changed in the query?

I tried different ways to execute the query like using OPENROWSET but it gives the error saying 'The OLE DB provider "IBMDASQL" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.' even though the SP returns records i still get this error.

Can anybody help me figure out a solution for this?

Thanks in advance

DB2 SQL - Possible to query a list of values without using "or" for each row of values?

I have a list of values and I am wondering if it's possible to query by said list, rather than having to do an "or" for each row of values or add them into a table and query the table (as I'm about to do)

Say, for example, my list is;

010, 46793, '329', '10'011, 46798, '322', '12'012, 33333, '111', '14'

I'd like to query like this;

SELECT VALUE1, VALUE2, VALUE3 FROM MYTABLE WHERE (VALUEW VALUEX, VALUEY, VALUEZ) in( (010, 46793, '329', '10'), (011, 46798, '322', '12'), (012, 33333, '111', '14'))

(This fails on syntax)

Rather than having to do;

SELECT VALUE1, VALUE2, VALUE3 FROM MYTABLE WHERE (VALUEW VALUEX, VALUEY, VALUEZ) = (010, 46793, '329', '10') OR (VALUEW VALUEX, VALUEY, VALUEZ) = (011, 46798, '322', '12') OR (VALUEW VALUEX, VALUEY, VALUEZ) = (012, 33333, '111', '14')

Please note that something like;

SELECT VALUE1, VALUE2, VALUE3 FROM MYTABLE WHERE VALUEW IN(010, 011, 012) AND VALUEX IN(46793, 46798, 33333) AND VALUEY IN('329', '322', '111') AND VALUEZ IN('10', '12', '14')

Will not work in this scenario.

For example, if the below value existed in "MYTABLE";

011, 33333, '329', '10'

The aforementioned SQL would retrieve it, yet it's not on my list.

Spring pagination is not working in DB2

I am having thousands of records in Database. I want to display the record using pagination in spring. I am able to fetch the first set of data but while fetching the second set of data, I am facing the issue.

I am using spring boot 1.5.8 Release and DB2 database version 11 and platform Z/OS with driver version 4.12

below given the details:

public void fetchDetails() { for(int pageCount=0; pageCount < pageTotalCount; pageCount++) { Page db2Data = db2Repository .findByIdIn(new ArrayList(Constant.LIST), new PageRequest(pageCount, 10)); System.out.println("db2Data :"+db2Data.getContent().size()); }}

Error:

15:42:28,990 INFO [stdout] (default task-2) Hibernate: select item0_.id as t1_1 from itm item0_ where item0_.id in (?) fetch first 10 rows onlydb2Data :1015:55:37,270 INFO [stdout] (default task-2) Hibernate: select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( select item0_.id as t1_1 from itm item0_ where item0_.id in (?) fetch first 20 rows only ) as inner2_ ) as inner1_ where rownumber_ > 10 order by rownumber_15:55:37,744 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -199, SQLState: 4260115:55:37,746 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;;??( [ DESC ASC NULLS RANGE CONCAT || / MICROSECONDS MICROSECOND, DRIVER=4.18.6015:55:37,747 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -516, SQLState: 2650115:55:37,748 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=4.18.6015:55:37,749 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Error: -514, SQLState: 2650115:55:37,749 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLN200C1, DRIVER=4.18.6015:55:40,031 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) SQL Warning Code: 4223, SQLState: null15:55:40,031 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-2) Origination unknown: [10228][11541][4.18.60] Security exceptions occurred while loading driver. ERRORCODE=4223, SQLSTATE=null15:55:40,049 INFO [stdout] (default task-2) 2017-12-14 15:55:40{EST} [default task-2] ERROR o.s.boot.web.support.ErrorPageFilter - Forwarding to error page from request [/copyAllItem] due to exception [could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet]15:55:40,049 INFO [stdout] (default task-2) org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet15:55:40,049 INFO [stdout] (default task-2) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:488)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)15:55:40,049 INFO [stdout] (default task-2) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)15:55:40,049 INFO [stdout] (default task-2) at com.sun.proxy.$Proxy281.findByIdIn(Unknown Source)15:55:40,049 INFO [stdout] (default task-2) at com.test.ItemServiceImpl.copyAllItem(ItemServiceImpl.java:308)15:55:40,050 INFO [stdout] (default task-2) at com.test.ItemController.copyAllItem(ItemController.java:70)15:55:40,050 INFO [stdout] (default task-2) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
Using MS Access to check for invalid DB2 characters

My job consists of me loading massive amounts of data into our DB2 database, which usually comes in the form of an Excel Spreadsheet from our customers. Our processes require us to load the data into MS Access to format the data first, then loading the data from there. Formatting the data is part of what I do, but we consistently run into issues where there will be a character in their data that will not load into DB2, and it will crash upon loading.

I've taken the time to create a table in MS Access that has a list of all of the characters that our system will accept, and I want to know if there is a way to check all of the data in the customer's spreadsheet against that list to validate that all of the characters used are in the "whitelisted" table, and if they aren't there, then delete them.

Any advice would be a huge help. I'm inexperienced in VBA, but I'm sure that is where I would have the best luck.

Thank you for any helpful suggestions!

EDIT:

VBA code did the trick, here is what worked for me.

Const Whitelist = "1234567890" & _ "qwertyuiopasdfghjklzxcvbnm" & _ "QWERTYUIOPASDFGHJKLZXCVBNM" & _ " `~!@#$%^&*()_-=+[]{};:""'|\<>?/ --" ' You can add or delete any characters to the whitelist manually hereEnd SubPublic Function isAllowed(char As String) As Boolean isAllowed = InStr(1, Whitelist, char, vbBinaryCompare) > 0End FunctionPublic Function Clean(dirty As String) As String'-- Returns only printable characters from dirty Dim x As Integer Dim c As String For x = 1 To Len(dirty) c = Mid(dirty, x, 1) If isAllowed(c) Then Clean = Clean & c End If Next xEnd FunctionSub CleanseField Dim d As Database Dim r As Recordset 'Use the column name as the fldName Dim fldName As Field Set d = CurrentDB() Set r = d.OpenRecordset("TableName") Set fldName = r.Fields("fldName") While Not r.EOF r.Edit fldName = Clean(CStr(fldname)) r.Update r.MoveNext Wend r.CloseEnd Sub
IBM DB2 installation and license issue

I have installed DB2 in my system V 11.1. using the command prompt i am able to open DB2 CLP. but when i enter a query such as "db2 connect to sample" it does not give me any output even i have created a sample database. same for other queries like query to create a database. when i enter a query it goes to the same line again where we enter a query no output is given. when i entered a query to check license it gave me output and all license were expired but i have currently installed the DB2 a day before. what could be the problem with this please reply.

IF SET Help Oracle to DB2

I have to migrate all procedures from Oracle to DB2 database. I'd like to know, how do I do SET syntax inside of IF as Oracle works.

Variable has been using in Oracle is ps_iPkCooperativa and are receiving 0.

Oracle Code :

BEGIN SELECT CCOOP INTO ps_iPkCooperativa FROM COOP EXCEPTION WHEN NO_DATA_FOUND THEN ps_iPkCooperativa := 0;END; 

How do I do this in DB2 ?

Convert dd-mon-yy string to date in DB2

in DB2 I've got date values stored as varchar in the form 'DD-Mon-YY' (e.g. 25-Jun-13). I'd like to convert these into DB2 compatible date formats using the TO_DATE function but every date conversion format I've tried gives me an error.

e.g. I've tried TO_DATE('25-Jun-13', 'YYYYMMDD') and TO_DATE('25-Jun-13', DDMMYYYY) and I always get something like "25-Jun-13" cannot be interpreted using format string "DDMMYYYY" for the TIMESTAMP_FORMAT function.

Does anyone know if there is a format string that I can use?

Bitronix with DB2 in spring boot

My configuration works fine if I define a datasource bean with driver properties containing driverType as 4.

If I let spring boot auto configuration create the data source bean, data source is created with driver type 2 by default as it's hard coded in the driver.

Is creating a bean explicitly the only way?

ASP.NET -- IIS7 -- IBM DB2 Issue

I'm working on a ASP.NET website that calls a DB2 database. I have Visual Studio installed on a Windows 2008 server where the site will be hosted. When I debug the site in Visual Studio, using the integrated web server, I can connect to the database and the site runs fine. When I set the site up in IIS7, on the same server, I cannot access the site or the database because of the following error.

Here are the technical details:

Windows 2008 ServerIIS 7Visual Studio 2010 PremiumDB2 v9.5.301.436Could not load file or assembly 'IBM.Data.DB2' or one of its dependencies. An attempt was made to load a program with an incorrect format. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.BadImageFormatException: Could not load file or assembly 'IBM.Data.DB2' or one of its dependencies. An attempt was made to load a program with an incorrect format.Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Assembly Load Trace: The following information can be helpful to determine why the assembly 'IBM.Data.DB2' could not be loaded.WRN: Assembly binding logging is turned OFF.To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.Note: There is some performance penalty associated with assembly bind failure logging.To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog]. Stack Trace: [BadImageFormatException: Could not load file or assembly 'IBM.Data.DB2' or one of its dependencies. An attempt was made to load a program with an incorrect format.] System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +0 System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks) +567 System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +192 System.Reflection.Assembly.Load(String assemblyString) +35 System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +118[ConfigurationErrorsException: Could not load file or assembly 'IBM.Data.DB2' or one of its dependencies. An attempt was made to load a program with an incorrect format.] System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +11424435 System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +484 System.Web.Configuration.AssemblyInfo.get_AssemblyInternal() +127 System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +334 System.Web.Compilation.BuildManager.CallPreStartInitMethods() +280 System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters, PolicyLevel policyLevel, Exception appDomainCreationException) +1167[HttpException (0x80004005): Could not load file or assembly 'IBM.Data.DB2' or one of its dependencies. An attempt was made to load a program with an incorrect format.] System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +11556592 System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +141 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +4813333

I don't understand how the site can run fine using the Visual Studio web server and not work in IIS. I don't know if it's an IIS issue or and issue with the version of IMB.Data.DB2.dll I'm using. If anyone has a solution or any ideas, they would be greatly appreciated.

Thank you.