FAQ Database Discussion Community


Executing DDL in compound SQL using DashDB (DB2)

sql,db2,dashdb
I need to execute a DDL command (CREATE TABLE) with other SQL commands. See the code snippet below: CREATE TABLE test AS ( SELECT duration AS NUM1 FROM event WHERE duration IS NOT NULL )WITH NO DATA; INSERT INTO test ( SELECT duration AS NUM1 FROM event WHERE event_duration_tech IS...

IBM Rational System Architect using DB2

sql-server,db2,ibm
Currently I am looking to setting up IBM Rational System Architect 11.4.3.0, however I need to get a database setup first to connect with it. I see from the system requirements it only supports SQL Server/Oracle. Due to various restrictions, I am unable to use this currently and the only...

Pull data from two DB2 tables, UNION ALL

sql,db2
I have two tables.. In output result I want to merge data from both. So assume I have to use UNION statement. What I need in result set: rows 1 and 2 from TABLE1 only. all rows from TABLE2 where TABLE2/COL4 = TABLE1/COL1 in these were selected in #1, (these...

Db2 .SqlIntegrityConstraintViolationException: SQLCODE=-803, SQLSTATE=23505

java,scala,db2
I am reading from a table in Oracle and inserting the entire dump into Db2. The table structures are the same.I am using Simple scala class which does the above mentioned task. I have set the insert batchsize as 300. After a few batches gets updated, the class is throwing...

Db2 sql for partition by range select

db2
I am trying to get my head around db2 partition stuff. I think they hired an alien to write their documentation! So i have sql Select a.*, max(a.bloo) over ( partition by range (a.bloo) (starting '2014-4-20' ending '2015-1-1') ) as maxmax from ( select * from someTable ) a I...

How to implement paging mechanism in generic way for SQL standards?

sql,sql-server,oracle,db2
In a project, we have successfully implemented the paging mechanism for SQL Server, but as per our requirement, we are trying to implement the paging mechanism in generic way for Oracle, SQL Server, DB2 & DB400 databases. Is it possible to implement paging in such way? What can be the...

SQL to find set of children with common parents

sql,db2
In my db2 database I have three tables. Computer Computer_Software Software This stores the relationship that each computer has multiple software items installed. So Computer_Software just has a foreign key to Computer and Software. I need a query that returns for a given set of computers, the list of software...

select statement for sum of column value in db2

sql,db2,db2400,cumulative-sum
I have this data in db2 which I need a query for... location | part | quantity -------------------------- loc1 TD3 300 loc1 RA5 0 loc1 BC4 200 loc2 MO2 4 loc2 CY1 0 loc4 RA5 100 loc4 PL5 400 loc3 YT7 2 loc3 UA9 5 The result set would return...

Syntax error when creating stored procedure in DB2

sql,stored-procedures,cursor,db2
I am trying to create a stored procedure in DB2 which uses a cursor. However, I am having issues with the correct syntax and recieve the error: SQL0104N An unexpected token "=" was found following "N FOR SET v_party_id". Expected tokens may include: "JOIN". LINE NUMBER=12. SQLSTATE=42601 My procedure is...

SQL - How to select from mulitple possible columns names?

sql,select,db2,case
I need to get data from several tables, but the last piece of data I need can come from one of three tables, and each table uses a unique column name. I've tried setting up a CASE statement in the select, but I've obviously made a syntax error. Am I...

SQL - find two consecutive rows for the same ID

sql,db2
We have table like this: ID PERSON GROUP ASSIGNEDGROUP CHANGEDATE 1 null GROUP1 GROUP1 01.01.2014 1 NAME1 null GROUP1 02.01.2014 1 null GROUP2 GROUP2 03.01.2014 2 null GROUP1 GROUP1 04.01.2014 2 NAME1 null GROUP1 05.01.2014 2 null GROUP2 GROUP2 06.01.2014 2 null GROUP3 GROUP3 07.01.2014 We would like to find...

Batch: unknown filename into variable to import it later into db2 table

batch-file,db2
Simple thing I want to do, but it doesn't work since the filename is not found: I have a .txt file with an unknown filename that will be later imported into a db2 table. I was thinking about using something like this: SET FILEDIR=\\FileServer\TestDir SET FILE=%FILEDIR%\*.txt db2 -wz%LOG% import from...

Use Row_Number To Number Records in DB2 (And Reset Count on Change of ID)

sql,db2,row-number
What I'd like to do is number the records the query returns, but reset the number to 1 on the change of a particular field. For instance, I would like the Row_Number count to reset back to 1 each time DCACCT changes but increment by 1 when there are multiple...

Connecting Android application to BlueMix stored DB2 Database

android,database,db2,database-connection,bluemix
How can I connect an Android application to a DB2 database stored in BlueMix? I know that some Java libraries are missing in Android, so I can't use JDBC to connect to the database. Any Idea?...

DB2 Resource BIND Error: DSNT500I -DBS1 DSNTBCM1 RESOURCE UNAVAILABLE

db2,tivoli
Trying to run a BIND job for the new Tivoli OMEGAMON XE DB2 upgrade. I'm having difficulties running the RKD2SAM(OMBPSS01) job to bind the OMEGAMON Server DB2 plan KO2PLAN. JCL: //OMEGAPL EXEC PGM=IKJEFT01 //STEPLIB DD DISP=SHR,DSN=SYS1.DB2DR.DSNLOAD //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DBS1) BIND PLAN (KO2PLAN)...

How to access a Row Type within an Array Type in DB2 SQL PL

arrays,stored-procedures,types,db2,row
I have a java front end that has a table of data. We need to save this data to the Database via stored procedure. If the passed parameter is a just an array, I am able to access the contents easily. But if the contents of the array is a...

Including DB2 Environment with WPF Build

c#,wpf,db2
I've been building a WPF program which makes a query to a DB2 database. During testing I ran into no problems but when I migrated a build of the program to another computer it would crash. I was able to debug using VS and got this error: The ‘IBMDADB2’ provider...

Recursive Query for Date Range

sql,db2
I'm trying to create a query so that I can generate a date range between a specific start and end point. I have the following: WITH DATE_RANGE(DATE_FOR_SHIFT) AS (SELECT DATE('2015-04-01') FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT DATE_FOR_SHIFT + 1 DAY FROM DATE_RANGE WHERE DATE_FOR_SHIFT <= @END) SELECT DATE_FOR_SHIFT FROM DATE_RANGE; Output...

Multiple row count from single table

sql,select,db2
How do I get counts of multiple records from a single table using db2 query? Suppose I want to get the count of 1 record am using: select count(*) from schema.table where record value='x' What I need is a count of multiple records from the same table in separate rows...

sum NULL or converting NULL to 0 to sum sql db2.iseries

sql,db2
I am a newbee at SQL and am currently trying to query a DB2.iSeries database, and I am stuck. This is my code: SELECT IPROD, IDESC, IMRP, NONAV FROM (SELECT IPROD, IDESC, IMRP FROM IIM WHERE IBUYC IN (<pln.value>) AND IMRP = 'N') AS IM INNER JOIN (SELECT I01PROD FROM...

sql convert from “record per month” to “record from/until”

sql,sql-server,tsql,db2
We've a database that stores the values of employees per month (for example the part-time percentage): +-----+------+-------+----------+ | emp | year | month | parttime | +-----+------+-------+----------+ | 1 | 2015 | 1 | 100 | | 1 | 2015 | 2 | 100 | | 1 | 2015 |...

Priority in DB2

group-by,db2,priority
This is my Parent_Child Table. PARENT CHILD TOT_BAL_AMT EFF_DT END_DT REFN_ID 22716 2528 5632 3/2/2015 3/5/2015 4112 22716 5698 5632 3/2/2015 5/9/2014 2563 22716 3256 5896 5/6/2014 5/6/2013 4555 37091 7412 4563 5/6/2015 1/1/2015 5666 37091 9632 4563 3/25/2015 2/5/2015 7896 425696 25 9000 3/8/2014 1/1/2015 45174 425696 30 8000...

Persisting user/db object in PHP MVC

php,model-view-controller,db2
I'm working on a web app, roughly using the MVC architecture outlined in the link below http://www.phpro.org/tutorials/Model-View-Controller-MVC.html I have created a custom class for logging in as described below. The user object is initialized in index.php. All requests are routed via index.php using mod_rewrite. How can I make the user...

Cannot initialise HikariCP pooled connection, Failure in loading native library db2jcct2

jdbc,db2,hikaricp
I'm trying to use HikariCP together with DB2 but get the following error: Failure in loading native library db2jcct2, java.lang.UnsatisfiedLinkError: db2jcct2 I have db2jcc4.jar file at my class path and only it. And the following hikari properties file: dataSourceClassName=com.ibm.db2.jcc.DB2SimpleDataSource dataSource.user=username dataSource.password=password dataSource.databaseName=database dataSource.serverName=server:50000 From what I understand Hikari tries to...

db2 not able to load output of uniq command in linux

bash,shell,db2
I have a file with below data: <row num="1"> <INN>20003943 </INN> </row> <row num="2"> <INN>20003943 </INN> </row> <row num="3"> <INN>20003943 </INN> </row> Notice that INN value is always same: 20003943. So uniq must return single value always which should go into INN column. I am having the below command to...

Select from each group the last n rows orders by date in DB2

sql,database,select,db2
I'm using DB2 database. I have a table that contains the following columns: Group | Name | Date | Value I want to select the 10 last values (ordered by date) for each Group. How can I do that in DB2 ? Many thanks. UPDATE : Here is what I...

NoSQL, ElasticSearch or even old school relational database?

elasticsearch,nosql,db2
we have an application that stores records of events in a DB2 database. And we have users that query these events, based on the time of the events, and optional some ids as search criteria. The data is only once written, no updates on these records. The query has no...

db2 query in shell script doesn't run with empty results in shell

bash,shell,db2
I have below script in shell. test.sh #! /bin/bash connect_stat=$(db2 -x "connect to $DB_NAME USER $DB_USER using $DB_PASSWORD" ) db2 "SET SCHEMA=SCHEMA1" while read line; do a=$(db2 -x "SELECT C.id FROM table C WHERE C.col1 IN ('$line)') with ur") echo $a done<inputs.txt I get empty results when I run "sh...

DB2 Join Query that Maximizes Column

sql,db2
I am OK at basic SQL, but my understanding fails when it comes to more complex searches. Right now, I am trying to return records for Location 16. Most records are empty, so I cannot debug my database application with those records. SELECT I.PART_NUM, I.ID, L.SHELF, L.IN_STOCK FROM INVENTORY I...

Merge Query in DB2

sql,merge,sql-update,db2
I need to update few columns in one table with the very convoluted calculation. I'm not good enough in SQL so I tried to use "with" clause in combination with update, but It threw error. Then I found a post online which suggested to use MERGE so I came up...

Entity Framework Is Null, Is Not Null Using DB2

c#,entity-framework,db2
I tried this: <pre>var query = this.context.PERFIL_ALERTA.Where(x => x.CodigoEmpresa == EmpresaId && x.IdAtividade == null);<code> But Entity do this: SELECT CAST(NULL AS decimal(18,2)) AS C1, CAST(NULL AS decimal(18,2)) AS C2, CAST(NULL AS decimal(18,2)) AS C3, CAST(NULL AS varchar(1)) AS C4, CAST(NULL AS decimal(18,2)) AS C5, CAST(NULL AS decimal(18,2)) AS C6,...

How to view Journal data of a Table Green screen DB2

db2,ibm,ibm-midrange,db2400
How do you view journal data from a specified table? I want to see what happended to a table in a specified time frame. I got info on the table by using the following command: DSPFD FILE(P6PRDBDB00/P6OIDPF) Got the journaled name and library but I dont know how to view...

ODBC Data Source Administrator - missing tcp/ip for installed driver

db2,odbc
When I open the ODBC Data Source Administrator (32-bit) to configure it for DB2 connections, I try to add a System DSN, and then add a DB2 database alias. The issue is that when adding the alias, the CLI/ODBC Settings don't show a TCP/IP tab so that I can enter...

pad smallint with 0s in DB2 sql for z/os

sql,database,db2,zos
is there any way to pad 0's to smallint in DB2 for mainframe z/OS. I am not writing any program but running query directly on DB2 QMF. Field definition: Column Name Col type length some_column Smallint 2 some_column result ----------- ------ 288 ==> 0288 88 ==> 0088 1224 ==> 1224...

SQL: Group by date and summing values in a column

sql,oracle,jdbc,db2
I have a JDBC database (DB2 specifically but am looking for something DB agnostic, at a minimum DB2 and Oracle) that has a table that, every 10 minutes, gets records inserted with statistics about APIs that are run by the application in question. It looks something like: StatKey, StartDate, EndDate,...

JDBC Columns not recognized

java,database,java-ee,db2
I've been trying to make a login page and have run into a problem with pulling information from the database containing User credentials. When running the code I get the following error "Error: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=USERNAME, DRIVER=3.68.61" meaning (I think) that the column username cannot be located,...

How to Divide two columns in DB2 SQL

sql,db2
I have disk capacities stored in 2 columns as CAPACITY_TOTAL and CAPACITY_USED. I need to present % utilization for which I need to divide CAPACITY_USED by CAPACITY_TOTAL as CAPACITY_USED/CAPACITY_TOTAL * 100. I am using below Query: select CAPACITY_USED / CAPACITY_TOTAL from TableName which gives below error: DB2 SQL Error: SQLCODE=-801,...

Java mapping for COBOL comp and comp-3 fields

java,db2,cobol
I am invoking DB2 stored procedure created using COBOL from my java application. input macro (type varchar): 01 SP1-INPUTS. 05 FIELD-1 PIC X(03). 05 FIELD-2 PIC S9(09) COMP. 05 FIELD-3 PIC S9(15)V9(02) COMP-3. 05 FIELD-3X REDEFINES FIELD-3 PIC X(09) To test the stored procedure, I know only value for FIELD-1....

Drop DB2 database if exist

db2
I would like to write db2 command which find out first database exist or not and if exist that data base then drop this database and create a new updated database . Please help for same...

Append data to a DB2 blob

java,jdbc,db2
In my DB2 database, I have a table with a Blob: CREATE TABLE FILE_STORAGE ( FILE_STORAGE_ID integer, DATA blob(2147483647), CONSTRAINT PK_FILE_STORAGE PRIMARY KEY (FILE_STORAGE_ID)); Using the db2jcc JDBC driver (db2jcc4-9.7.jar), I can read and write data in this table without any problems. Now I need to be able to append...

How to connect to DB2 SQL database with node JS?

node.js,db2
For linux, one can use the API page found here, and connecting is straight forward. For OS X, one will run into the error when trying to connect to an existing DB2 database: { [Error: [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004 ] error: '[node-odbc] SQL_ERROR', message: '[IBM][CLI...

Quantity of transaction logs used per application/connection in DB2

db2,db2-luw
I would like to know the quantity of logs used (active logs) by each connection in the database. I know how to retrieve the quantity of active logs for the database, but not for each application. Knowing the quantity of active logs in the database helps me to identify if...

How can I convert a PHP application in Bluemix to use SQL Database instead of mySQL database?

php,mysql,db2,bluemix
I have been following this tutorial: "Build and deploy a REST API on IBM Bluemix with PHP and MySQL." The tutorial uses mySQL. I want to use the SQL Database service, which uses DB2, I think. The tutorial implements a REST API. When I try making a REST call using...

SQL Having statement with Date Range - The grouping is inconsistent

sql,db2,ibm
I'm struggling with this script. I want to write it where it'll calculate the total of instances then add the sum of those instances, however, when I run the script with the "HAVING....AND CSH.SLS_DT BETWEEN '2015-01-01' and '2015-06-16'" I get an error stating that "the grouping is inconsistent". The script...

Creating views in as400 db2

db2,ibm-midrange
If I create a view in db2, say something like: select RNN(sometable) as rn, c1,c2,c3 from sometable order by rn desc fetch first 100 rows only This would essentially pull the last 100 rows from the table (I use RNN because there's nothing else to base that sort by, sadly....

Using @ on Variable Names

db2,db2i
Googling I've found this DB2 Function declaration: CREATE FUNCTION QGPL.SPLIT ( @Data VARCHAR(32000), @Delimiter VARCHAR(5) ) Whats means @ symbol before the Variable Name? Regards, Pedro...

Synchronize some tables from two databases in different DBMS

mysql,sql,db2
I want to synchronize some tables from two databases in different DBMS (DB2 and MySQL). How can I do that without writing code in any of the applications that use these databases? Any idea? The application that use DB2 has been written in Java, the other one is a SuiteCRM...

How to connect PHP web service on BlueMix and Android app? [closed]

php,android,json,db2,bluemix
I need to create a connection between a SQL DB2 database located on BlueMix and an Android App. To do so, I wanted to create a web service to handle the communication between the database and the app using JSON data. This web service is written in PHP, which needs...

Getting the multiple count() in a sinle select statement without script repitiion

sql,database,db2,correlated-subquery
Below is a script to get the count in a single select with lot of code redundancy. How really I can clean up the code? SELECT (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY =...

How to connect Db2 Federated Views with Hibernate 4

db2,federated-table
Can any one please suggest or help how can we have Hibernate mapping with Federated Views in Db2 Database? Database_1 : TABLENAME001 Database_2 : FV_XXX_TABLENAME [FV is federated view with Database_1 table TABLENAME001] Now when I run through normal JDBC like select * from FV_XXX_TABLENAME I get the output. But...

Java, passing database connection into different classes.

java,database,db2,database-connection
I don't understand the purpose of DatabaseAccess class. I'm curious about how did the object name "conDA" contains the database connection even without using the getConnection() method I created. Example "con2 = new Connection2(conDA);" how is that "conDA" object name can be understood as a connection of database? Here are...

SQL Date Conversion Query

sql,db2,date-conversion
TimeStamp is a numeric datatype field in my table R for example: 2445302102010 (02/10/2010) I am trying to query by a date range of 02/09/15 - 02/15/15, the problem is I am getting results from 2010? Select distinct R.CID, RIGHT(R.TimeStamp,8) from TableRev R WHERE R.Codes in ('NY','NV') AND RIGHT(R.TimeStamp,8) between...

1 Working Day Back In SQL DB2

sql,vba,db2
I need to select data from database with a date that is one day back into the past but on working day. Is there a specific function for that ( Excel has got formula =Workday)? I am downloading data from DB2 to excel with VBA instructions. Here is the piece...

Using field alias in MSQuery does not work with DB2

db2,ms-query,ibm-data-studio
This query works in data studio, but fails to show alias in MS Query! I have tried different types such as "",'',[] and even https://support.microsoft.com/en-us/kb/298955 SELECT 'TRANIN'AS NAME, SUM(CASE WHEN ALT3.TRANINDT BETWEEN 20150603 AND 20150601 THEN 1 else 0 END) AS CurrentMonth, SUM(CASE WHEN ALT3.TRANINDT BETWEEN 20150501 AND 20150531 THEN...

Migration of XML functionality from Oracle to DB2 LUW (ver 9.7 on AIX)

xml,oracle,plsql,db2,procedure
We are trying to migrate a set of Oracle PL/SQL's to DB2. We have set the Oracle compatibility vector in the db2 configuration parameters, but we are facing issues in instances where an xml file is being read in the Oracle PL/SQL code, eg:- v_parser dbms_xmlparser.Parser; v_doc dbms_xmldom.DOMDocument; v_nl dbms_xmldom.DOMNodeList;...

How do I use ROW_NUMBER in DB2 10 on z/OS?

db2,row-number,zos
I am running a SQL query and trying to break the results down into chunks. select task_id, owner_cnum from (select row_number() over(order by owner_cnum, task_id) as this_row, wdpt.vtasks.* from wdpt.vtasks) where this_row between 1 and 5; That SQL works with DB2 10.5 on Windows and Linux, but fails on DB2...

join 2 tables and get some columns from 1st table and max timestamp value from second table

sql,join,db2,max,left-join
I have a employee table empid empname status 1 raj active 2 ravi active 3 ramu active 4 dan active 5 sam inactive I have another table called facilities empid timestamp 1 2014-12-28 1 2015-05-05 1 2015-06-05 2 2015-05-03 2 2015-06-04 3 2015-02-01 I want my result like empid empname...

published reports don't work - Database logon failed Error

c#,iis,visual-studio-2013,crystal-reports,db2
I am developing a simple web app that has 3 reports created on VS 2013, for some reason those reports run fine from developer mode, but when I publish the website they give me "Database logon failed" Error. What could be causing this? VS is installed on the same server...

DB2 restrict analytic function to subselect

sql,db2,olap
I often encountered the situation where I wanted to restrict the action of an analytic function (or OLAP in DB2) to a certain subset of the present data. Here is an example: WITH MY_TABLE AS ( SELECT 1 AS FIELD1, 'A' AS FIELD2, 1 AS FIELD3, 'X' AS FIELD4 FROM...

Running a scheduler on more than one machine

java,db2,locking,scheduling
I have a Java service which creates x games in the future every y seconds (calculating start time and end time for each of them) and inserts them into a DB2 table. The code basically looks for the end time of the last created game, if it exists and is...

strange DB2 Exception while executing queries

java,stored-procedures,db2
I am running some DB2 queries and after that I am calling a PL sql procedure calling of Procedure is CallableStatement callStmt = con.prepareCall( // "CALL INIT_PAYROLL(?,?,?,?,?,?,?,?,?,?)"); // Set IN parameters callStmt.setString(1, start_date);//IN callStmt.setString(2, end_date); //IN callStmt.setInt(3, customer_id);//IN callStmt.setString(4, payrollMain_id+"");//IN callStmt.setString(5, ruleFreq );//IN callStmt.setInt(6, 0); callStmt.setString(7, outerArray);//IN callStmt.setString(8, commsSepEmps);//IN callStmt.setInt(9,...

How to “combine” arrays

php,db2
I am query a DB2 flat file and a regular table to get information. The most current data will be coming from the not flat file. I'm putting both results into two separate arrays. Ideally I think joining the tables would have been best, but I tried and I don't...

How to add leading spaces to output column

sql,db2
Here is my SQL statement: SELECT col1 AS MYCOL FROM table 1 UNION SELECT col2 AS MYCOL FROM table 2 I need to add some spaces to col2 of table2 in output results so it looks like a tree: MYCOL row 1 row 2 row 2.1 row 2.2 row 3...

Liquibase generateChangeLog command - generating changelog with insert statements

oracle,db2,liquibase,dml
To generate insert statements from my databases (oracle,db2) i've used liquibase generateChangeLog command with argument --diffTypes="data" This command generate correct xml with insert statements, however this is not aware of foreign constraints, so I cannot use this file to again to fill my databases. Similar problems has been described here...

Hibernate error for DB2 10.5 Connection refused: connect. ERRORCODE=-4499, SQLSTATE=08001

java,hibernate,db2
I am new Hibernate. I am trying to run my first Hibernate example. I have installed db2 10.5 server in my local system. My Hibernate config file: <?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="connection.driver_class">com.ibm.db2.jcc.DB2Driver</property> <property...

Unable to install (re-install) Toad DB2

installation,db2,toad
When I try to re-install Toad, i get the following error message: "The Toad for DB2 installer has detected an existing 64-bit DB2 environment on your computer. This installation of Toad for DB2 includes a 32-bit DB2 client. However, DB2 does not allow both 32-bit and 64bit DB2 Environment to...

Need help in SQL Query

db2
Can anyone please help me in writing a single query joining these two queries. I am using IBM DB2. (SELECT TABLE1.COLS,TBLE2.COLS,TABLE3.COLS FROM TABLE1,TABLE2,TABLE3,TABLE_PROB WHERE TABLE_PROB.COL=TABLE1.COL,OTHER_CLAUSE ) UNION (SELECT TABLE1.COLS,TBLE2.COLS,TABLE3.COLS FROM TABLE1,TABLE2,TABLE3,TABLE_PROB1 WHERE TABLE_PROB1.COL=TABLE1.COL,OTHER_CLAUSE ) The two queries before and after union are same except that instead of "TABLE_PROB" it is...

Need help in Group by in DB2

group-by,db2
This is Parent_Child table. PARENT CHILD EFF_DATE 22716 2528 3/8/2011 22716 5696 3/8/2011 22716 3698 3/8/2011 22716 5698 3/18/2010 37091 4569 10/22/2013 37091 6931 9/17/2014 Query result should look like this: PARENT CHILD EFF_DATE 22716 2528 3/8/2011 22716 5696 3/8/2011 22716 3698 3/8/2011 37091 6931 9/17/2014 Query tried: SELECT DISTINCT...

'pecl install ibm_db2' can't find library

linux,db2,rhel,pecl
I'm trying to install ibm_db2 through pecl: pecl install ibm_db2 Then when it asks for an install dir, I have tried various places only to have the same result. It goes through a bunch of checks etc., then tries to 'make' and gets an error: /usr/bin/ld: skipping incompatible /home/db2inst1/sqllib/lib32//libdb2.so when...

IBM Data Studio can't browse data on SAMPLE (DB2 Express-C)

db2,ibm-data-studio
I can't browse data on SAMPLE DB. Setup is as follows: - Windows 7 64 bit PRO, - IBM DB2 Express-C, 10.5.500.107 (latest) - IBM Data Studio Version 4.1.1 (Administrative installation, Installed using IBM Installation Manager 1.8.1) Database credentials are: db2admin/db2admin Windows administrator username is: Nenad (password protected) With DB2...

Hibernate isolation levels vs database isolation levels

hibernate,db2,isolation-level
If Hibernate is used as the ORM tool and DB2 as the database in Hibernate we can define Isolation levels and DB2 has its own isolation levels. What is the benefit that we get by defining the isolation levels in hibernate vs using the database isolation levels?

Include “0” counts in result set, DB2

sql,db2
I am running a query on DB2 as such: SELECT column_1, COUNT(*) AS "my_count" FROM "my_table" WHERE column_1 IN(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AND another_column = '20150609' GROUP BY column_1; This works fine in terms of getting row counts grouped by column_1. However, the problem...

How can I set up a default DB2 schema In DBArtisan 9.6

db2,database-connection,dbartisan
I am using DBArtisan 9.6.0 for accessing an IBM DB2 database. When entering a SQL query, DBArtisan forces me to prefix the schema name explicitly before every table name. I would like to set up a default schema, but searching the DBArtisan user guide for "default schema" no longer returns...

db2 - export into File

unix,db2,sh
I'm trying to export the result of a query in db2 to file, but the result like: +000000869836.|+00000000000001401. +000000978137.|+00000000000000511. What can I do to obtain the following result: 869836.|1401. 978137.|511. My current query: EXPORT TO /tmp/tmp.tmp OF del modified by NOCHARDEL coldel| select iv.bpd_instance_id, max(case when alias = 'abc' then...

Create relation one-to-many without Foreign Key in nhibernate4

c#,nhibernate,db2,nhibernate-mapping,nhibernate-4
i'm new to NHibernate 4 and i try to create a one-to-many relation between two table in DB2, the tables don't have a foreign key and i can't edit the tables. When debug the application i catch error in .saveorupdate method. The tables contains: TLDMAIN->summary of article TLDDETAIL->detail of article...

JPA in Java SE vs Java EE performance

java,java-ee,jpa,db2,eclipselink
I'm using JPA/Eclipselink in Java SE (Oracle Java 7) and Java EE (Websphere Liberty) environments. I have a component which is called from both Java SE and Java EE (by component I mean the same source code), which executes JPA queries. (the Java SE run is started with -javaagent:lib/eclipselink.jar param)...

DB2 Select from two tables when one table requires sum

sql,database,db2
In a DB2 Database, I want to do the following simple mathematics using a SQL query: AvailableStock = SupplyStock - DemandStock SupplyStock is stored in 1 table in 1 row, let's call this table the Supply table. So the Supply table has this data: ProductID | SupplyStock --------------------- 109 10...

Getting “failed to start accepting connection” while deploying my app into bluemix

node.js,db2,bluemix
Hi Am facing "failed to start accepting connection" error while pushing or deploying my app to bluemix. Can any help me in that.... my code snippet as given below: var express = require('express'); app = express(); var ibmbluemix = require('ibmbluemix') var ibmdb = require('ibm_db'); var http = require('http'); var url...

Convert varchar to time datatype in DB2

sql,db2
I have a source table with column named "created_time" whose data type is varchar(22). I need to migrate timestamp value of this column to target table in DB2 whose datatype is TIME. I tried different db2 timestamp and date related function but that's not working for me. Is there anyway...

select from insert into not working with sqlalchemy

python,sql,sqlalchemy,db2
I want to insert a record in mytable (in DB2 database) and get the id generated in that insert. I'm trying to do that with python 2.7. Here is what I did: import sqlalchemy from sqlalchemy import * import ibm_db_sa db2 = sqlalchemy.create_engine('ibm_db_sa://user:[email protected]:50001/mydatabase') sql = "select REPORT_ID from FINAL TABLE(insert...

Load period-separated text file into db2

db2,db2-luw
I need to load an entire text file (contains only ASCII text) into the database (DB2 Express ed.). The table has only two columns EXAMPLE_TABLE (ID, TEXT). The ID column is PK, with auto generated data, whereas the text is VARCHAR(50). Now I need to use the load/import utility to...

Initialize object in controller MVC

php,model-view-controller,db2,zend-server
I'm exprimenting with MVC from the ground up following this tutorial and getting 500 errors when trying to initialize a new object from a controller. http://www.phpro.org/tutorials/Model-View-Controller-MVC.html employeelist.class.php <?php class employeelist { private $employees = array(); public function getEmployees() { sql='SELECT F0101.ABAN8, F0101.ABALPH FROM F0101 INNER JOIN FP0102 ON F0101.ABAN8=FP0102.VEAN8 WHERE...

How to create boolean variable in a stored procedure using db2?

sql,db2
CREATE PROCEDURE deleteNotActualData() SPECIFIC proc_vars LANGUAGE SQL BEGIN DECLARE trueOrFalse BOOLEAN; END; When I try to do this query I have an: error: 42601 A character, token, or clause is invalid or missing. ...

DB2, when trying to calculate difference between provided and stored timestamp I get an error 'The invocation of function is ambiquious'

sql,jdbc,db2,db2-luw
This is my sql string from which I prepare statement: SELECT (DAYS(?) - DAYS(FROM)) * 86400 + (MIDNIGHT_SECONDS(?) - MIDNIGHT_SECONDS(FROM)) AS FROM_DIFF, (DAYS(?) - DAYS(TO)) * 86400 + (MIDNIGHT_SECONDS(?) - MIDNIGHT_SECONDS(TO)) AS TO_DIFF FROM CALENDAR.EVENTS WHERE ID = ? fill values with the following code: ps.setTimestamp(1, new Timestamp(...)); ps.setTimestamp(2, new...

Recursive Stored Procedures

sql,stored-procedures,recursion,db2
I found this code snipped (Source): CREATE PROCEDURE rec_fib(n INT, OUT out_fib INT) BEGIN DECLARE n_1 INT; DECLARE n_2 INT; IF (n=0) THEN SET out_fib=0; ELSEIF (n=1) then SET out_fib=1; ELSE CALL rec_fib(n-1,n_1); CALL rec_fib(n-2,n_2); SET out_fib=(n_1 + n_2); END IF; END This code works with MySQL. In how far...

See actual code of function in DB2 for IBM i

db2,ibm-midrange,dbvisualizer,jt400
Hello guy I create a function in DB2 UDB for AS/400 version 07.01.0000 V7R1m0 I use a windows with dbvisualizer to connect the server. My function is... CREATE FUNCTION JVAOBJ.BNOWPAPOL(POL VARCHAR(10)) RETURNS DECIMAL(7,7) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA RETURN ( SELECT CASE WHEN NUM IN (1,2) THEN 0.3...

Read in a file that contains multimple “CLOB” formatted data fields

db2
Reading in a file that contains multiple "CLOB" formatted data fields (fields are formatted text values up to 32K bytes in size). The text includes end of line characters, so our standard import wizards get confused and think that they've reached the end of line and start importing the next...

How can I write queries for the Bluemix SQL database

db2,bluemix
How can I create a table that includes auto increment and NOT NULL. I am using the sql database console that bluemix offers. The following query gives me an error. CREATE TABLE discounts ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, expired_date DATE NOT NULL, amount DECIMAL(10,2) NULL,...

DB2 Convert from YYYYMMDD to Date

sql,date,casting,db2
I have a column that stores a dat as char in the format 'YYYYMMDD'. Now I want to convert it to a real date. I tried select cast (DATEFIELD as DATE) as MyDate But it only returns the old YYYYMMDD format labeled as 'DATE'. What am I doing wrong?...

DB2 Z/OS 10 Equivalent of AUTONOMOUS procedure

stored-procedures,db2
Is it possible to get the functionality of autonomous stored procedure in DB2 z/OS 10? Scenario is: attempt to execute given stored procedure must logged to our logs table even if the procedure ended with exception (rollback was performed). I was going to use autonomous stored procedure for that, but...

SELECT field as mytest WHERE mytest IS NULL - field “mytest” not found?

sql,db2
I am trying to use a selected field's named alias in a where clause, yet the database complains about being unable to find said field. Query: SELECT somefunction(myfield) as mytest, myotherfield as mytest2 FROM database.table WHERE mytest IS NULL OR mytest2 IS NULL Expectation: The db would simply test the...

My sql statement has two parts attached with 'union all'. I want the second part to exceecute only when some condition is true

mysql,sql-server,db2,union
my query is too big to paste here and refers to at least 15 tables, so i am pasting a small example to explain my problem SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City; i want the union all...

Select a portion of a comma delimited string in DB2/DB2400

sql,db2,db2400
I need to select a value within a comma delimited string using only SQL. Is this possible? Data A B C 1 Luigi Apple,Banana,Pineapple,,Citrus I need to select specifically the 2nd item in column C, in this case banana. I need help. I cannot create new SQL functions, I can...

Select most recent records by DB2 date in form of YYYYMMDD

sql,db2
I am importing records from a DB2 data source into a MS SQL Server destination. The records are coming in with the date format of 20150302/YYYYMMDD, but I only want the last 14 days based on current server date. Can some advise on how to select based on this date...

Is it possible to activate the compatibility mode for Oracle in SQL Database?

db2,bluemix
Is there any way to activate compatibility mode for Oracle in any of the existing plans of SQL Database (DB2) in Bluemix? It seems by default is not activated. Thanks!

Permanently set DB2CLP environment variable

db2
I have a problem with DB2CLP environment variable, on Windows 7 64, IBM_client64_V97 (to be connected with DB2). Every time when i try to DB2 in Windows command prompt window, I receive this error: DB21061E Command line environment not initialized. and per this document using just this step: 2. at...

Trimming Blank Spaces in Char Column in DB2

casting,db2,trim
I'm trying to remove blank spaces that appear in a CHAR column within DB2. I received some helped here with the function TRANSLATE to determine if Left contained records that began with three letters: select pat.f1, hos.hpid, hos.hpcd from patall3 pat join hospidl1 hos on pat.f1=hos.hpacct where TRANSLATE( LEFT( hos.hpid,...

Using cursor for fetching multiple rows and setting its data in columns

sql,join,cursor,db2
My scenario is I have to populate data into a table by performing join on two tables A and B on the basis of column quoteid and compid Table A ------------------------------ quoteid compid ................ 10004 1 10004 1 10004 1 10004 22 10004 22 10004 22 Table B ------------------------------------ quoteid...

Can you check for a DB2 license in C#?

c#,wpf,db2,db2400
I've written a WPF app in C# (4.5) and it connects to a DB2 server. I want to know if there's a way to check the local machine for a valid DB2 license and display a warning if it's not found.

How to add db2 user as SYSADM

database,db2
I'm new to db2 and currently working with the environment which all the information are lost. The only known information is one user id as db2inst1. I need to grant SYSADM permissions to this user. Root password also didn't know. So how can I do that ? Is it possible...