I'm reading a record from a DB2 database via ODBC connection. The data is populated into an ODBCDataReader. As I'm going through my foreach loop I'm running into problems trying to parse the multiple different DateTime fields. Some of the fields are null, some have null date time value (9999-12-31 24:00:00.000000) and some have valid ...
I am getting this error: [OLE/DB provider returned message: [IBM][CLI Driver] [DB2 / linuxx8664] SQL0206N "LS_SCHEDULED_DATE" is not valid in the context where used. SQLSTATE=42703) OLE DB error trace [OLE /DB Provider 'MSDASQL' IColumnsInfo: GetColumnsInfo returned 0x80004005: Msg7339, Level 1, State 1, Line 83 OLE DB provider 'MSDASQL'...
I am receiving the error SQL0104N An unexpected token "," was found following "select .loan_number". Expected tokens may include: "". SQLSTATE=42601 IF OBJECT_ID('tempdb..#Temp1') is not null begin drop table #Temp1 select * into #Temp1 from openquery(LnkServer, ' Select X.loan_number, X.ls_code, x.ls_actual_completion_date x.ls_...
hi i have string type abc_01, abcd_01 or 02 now i want the substring upto _ ie abc_,abcd_ etc. I am using db2 as400 .Please suggest the processing through RIGHT or LEFT function ...
What am I missing in this code below that will cause the error: Msg 170, level 15, line 113 line 113: Incorrect syntax near 'actual_completion_date'. update #Loss_mit_step set [STEP924_COMPL_DATE] = Case when step_code ='924' then ls_actual_completion_date else ' ' end, [STEP926_COMPL_DATE] = Case when step_code ='926' then ...
Hello guys need help with a query. Consider the following table I need to select first the sum of each Code from table. I am doing it with simple sum and group by statement. Then I have to subtract the results from each code sum where type='r' 1)Say for first part of query, we will get 2 rows from SUM (one with total USD and one with...
Is it possible to create an index of a table based on substring of one or more field values in DB2 ? ...
Hello guys, consider the following example. I have to select all the records from table1 which exist in table2 also + all the records from table2 which don't exist in table1 but exist in table2 and have IsActive=1 and status not null. I initially tried it with a join but how to do the later part where I have to select the records whic...
Hi I have 2 tables. I want to list all records in table1 which are present in table2 all records in table2 which are not present in table1 with a where condition Null rows will be returned by table1 in second condition but I am unable to get the query working correctly. It is only returning null rows SELECT A.CLMSRNO,A.CLMPLAN...
Time line: 1_ Made backup of database 2_ Deleted a row from table that deleted +100 others records in other tables since they where with the constrait ON DELETE CASCADE. 3_ Made backup of database 4_ Made a lot valid of updates/deletes/inserts. 5_ Need to recover the data lost in 2 I've seen the rollforward db2 command, but it's n...
Hi, I need to alter multiple tables in a schema, for all tables prefixed with something, for example: ALTER TABLE "SCHEMA"."TABLE1" ADD ( "COLUMN1" CHARACTER(4) NOT NULL DEFAULT 'DATA', "COLUMN2" VARCHAR(16) NOT NULL DEFAULT 'MORE_DATA', ); I need this to iterate over multiple tables, like SCHEMA.table1, SCHEMA.ta...
Hi I am trying hard to write a stored procedure in ISeries DB2 but having errors. create procedure pakretst.fttest2 (IN fExpression CHARACTER(10)) language sql reads sql data dynamic result sets 1 begin declare stmt VARCHAR(50); declare x cursor for sl; If ftExpression IS NOT NULL set stmt='select * from pakretst.uwftrtystp W...
Hello guys, getting really close to running my first stored procedure. This one compiles but when I run it with call test.fttest5('YEAR'); it throws an error SQL State: 22001 Vendor Code: -303 Message: [SQL0303] Host variable *N not compatible. Cause . . . . . : A FETCH, SELECT, CALL, SET, VALUES INTO, GET DIAGNOSTICS, GET DESCRIPTO...
I have a need to run a recursive CTE within a stored proc, but I can't get it past this: SQL0104N An unexpected token "with" was found following "SET count=count+1; ". Expected tokens may include: "". LINE NUMBER=26. My google-fu showed a couple of similar topics, but none with resolution. The query functions as expected outside of the...
Hi, I am going to use a legacy DB2 on zOS as database in a banking project. I am proficient in programming on Oracle. Also I've used MySQL and SQL Server for many years but know nothing about DB2 and their SQL dialect and procedural language for writing stored procedures and functions. I am looking for good resources to learn DB2 archit...
I have an existing DB2 database at my job. (At least, I think it's DB2. They refer to it as "the iSeries" and it looks and feels like DB2 on some mainframe-ish hardware.) For years the .NET developers in my department have just manually written ADO commands and queries to get specific data from specific tables, etc. At the moment I'm...
Running DB2 8.2 Fixpak 18 for LUW, I need to convert a DECIMAL field to a IMPLIEDDECIMAL for a file export. I cannot find anything in the documentation online, please help. ...
I have an application that needs to query two different DB2 databases for the exact same data. Is there any way to create a view that takes my query, executes it against both databases, combines the results, and send them back to my application? ...
Hello guys, I want a stored procedure to return the primary key of the new record after it gets executed. I think it will be returned by OUT parameter in the procedure. But how to select the newly inserted row ID ? I don't want to use select MAX(row_id) as it is a multi user environment. Any procedure sample will be appreciated. My plat...
I am trying to execute a stored proc in java. I know I have to connect to database and do the stuff.... But the thing is How do I know that if that stored proc executed successfully? Thanks. ...