Permanently set DB2CLP environment variable
When DB2 client is installed, it should have installed a functional window to the command line processor. Check here: Start / All Programs / IBM DB2 / DB2copy1/ Command Line Processor Note that DB2copy1 is the default location for the first db2 instance. The name may be different if user...
SQL to find set of children with common parents
If your computer_software table has a column computer_id and software_id and there is one row per computer and software on that computer -- as I imagine -- then you can count the rows where the computer is either 1/2/5, group by computer_id, having a count equal to 3 (the software...
'pecl install ibm_db2' can't find library
So it turns out I didn't actually need to include these libraries in php.ini or through pecl (doing it that way meant it was looking on a path it couldn't find for some reason), because they were already set up from my PHP configure command. It put the headers in...
Unable to install (re-install) Toad DB2
O and here is the solution. Hope this can help others as well:) Opened registry, looked for the keys HKEY_LOCAL_MACHINE\SOFTWARE\IBM HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\IBM Just renamed the IBM Folder into something else After that the 64 bit Toad setup will pass the checking for any installed IBM software. And there you go! :)
Batch: unknown filename into variable to import it later into db2 table
SET FILEDIR=\\FileServer\TestDir rem SET FILE=%FILEDIR%\*.txt for %%# in ("\\FileServer\TestDir\*.txt") do set "file=%%#" db2 -wz%LOG% import from %FILE% of del method p(....) insert into tablename (.......) eventually you'll need to map the network drive if nothing is found....
How can I set up a default DB2 schema In DBArtisan 9.6
db2,database-connection,dbartisan
Don't know about dbartisan, but you can do it the DB2-way. Either using the parameter currentSchema in the connection String, e.g. (Java example): jdbc:db2://localhost:50000/MYDB:currentSchema=MYSCHEMA; Or in your SQL using set current schema before your other commands: set current schema MYSCHEMA; select * from MYTABLE; ...
strange DB2 Exception while executing queries
22001 means Character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a host variable, because it is too small. Here is the link with different DB2 error codes https://urssanj00.wordpress.com/2008/03/04/db2-sql-error-code-and-description/...
Priority in DB2
This can easily be done using window functions: select * from ( select pc.*, row_number() over (partition by parent order by tot_bal_amt desc, eff_dt desc, end_dt desc) as rn from parent_child pc ) t where rn = 1 order by parent; SQLFiddle example (using Postgres): http://sqlfiddle.com/#!15/70b10/2 For each parent, the...
How can I convert a PHP application in Bluemix to use SQL Database instead of mySQL database?
If you want to run it on Bluemix (via Cloud Foundry) you will need a PHP buildpack with DB2 driver support. This one should work: https://github.com/ibmdb/db2heroku-buildpack-php. Working sample here: Tip: to troubleshoot problems like this try the cf logs "APP_NAME" --recent command. Read more here...
Including DB2 Environment with WPF Build
The majority of the article you linked just describes how to find the download for the DB2 .NET provider. Really, this is something you only need to do once. Once you have the installer downloaded you can manually install it when you deploy the application, or build it into an...
Pull data from two DB2 tables, UNION ALL
select col1, col2, col3 from table1 WHERE COL1 IN (1,2) union select t2.col1, coalesce((select col2 from table1 WHERE COL1 IN (1,2) AND COL1 = t2.col4), t2.col2), t2.col3 from table2 t2 join table1 t1 on t1.col1 = t2.col1 and col4 in (select col1 from table1 WHERE COL1 IN (1,2)) Gives me:...
DB2 Convert from YYYYMMDD to Date
SELECT TIMESTAMP_FORMAT("DATEFIELD",'YYYYMMDD') as "MyDate"
SQL: Group by date and summing values in a column
Another possible solution: select to_char(StarDate,'rrrr-mm-dd HH24:')||'00' as DateHour, APIName as API, sum(StatValue) as Invocations from STATISTICS where StatName = 'Invocations' group by to_char(StarDate,'rrrr-mm-dd HH24:')||'00', APIName There are differents ways to do this.. Good luck!...
JDBC Columns not recognized
42703 means An undefined column, attribute, or parameter name was detected. Here is the link with different DB2 error codes https://urssanj00.wordpress.com/2008/03/04/db2-sql-error-code-and-description/...
Use Row_Number To Number Records in DB2 (And Reset Count on Change of ID)
You are looking for the partition by clause: select Row_Number() Over (partition By DCACCT order by DCACCT) as row_num, You might have a preference for the column used for ordering within each DCACCT value -- such as a creation date or id -- but this just uses the field itself...
How to Divide two columns in DB2 SQL
OK, so SQLSTATE=22012 means you're trying to divide by zero. Apparently some of your rows contain nulls or zeroes in CAPACITY_TOTAL column and db fails to calculate the formula. Try this query for better results: SELECT CASE CAPACITY_TOTAL WHEN 0 THEN 0 WHEN NULL THEN 0 ELSE CAPACITY_USED / CAPACITY_TOTAL...
Select a portion of a comma delimited string in DB2/DB2400
I am answering my own question now. It is impossible to do this with the built in functions within AS400 You have to create an UDF of Oracle's INSTR Enter this within STRSQL it will create a new function called INSTRB CREATE FUNCTION INSTRB (C1 VarChar(4000), C2 VarChar(4000), N integer,...
select statement for sum of column value in db2
I don't think you want "cumulative quantity", you just want the sum: select location, sum(quantity) from db2 group by location having sum(quantity) < 10; "cumulative" would imply a running sum or cumulative sum....
Need help in Group by in DB2
Changes required change inner query to group on parent instead of child change join condition to match on parent query SELECT DISTINCT P.PARENT,P.CHILD,C.MAX_DATE FROM parent_child P INNER JOIN (SELECT PARENT,MAX(EFF_DT) AS MAX_DATE FROM parent_child GROUP BY PARENT) C ON P.PARENT=C.PARENT AND P.EFF_DT=C.MAX_DATE ORDER BY P.PARENT ; output PARENT CHILD MAX_DATE...
Using cursor for fetching multiple rows and setting its data in columns
Table A seems to have nothing to do with your result. The basic idea is to use row_number() and conditional aggregation. This is complicated because you the quarter identifier is stored backwards, so it doesn't sort correctly. But you can still do it: select quoteid, compid, max(case when seqnum =...
Recursive Stored Procedures
sql,stored-procedures,recursion,db2
This works for me: (I haven't done more than make it work, so alternative coding could also work.) First, add these two lines: DECLARE n_3 INT; DECLARE n_4 INT; Then modify this small section: ELSE set n_3 = n - 1; set n_4 = n - 2; CALL rec_fib(n_3,n_1); CALL...
Using field alias in MSQuery does not work with DB2
MS broke MS query a long time ago... I've tried to get it to work right, but nothing worked. I've pretty much given up. Normally I just rename the column once the data is back in Excel. But if you really want the name returned from MS query, this works:...
DB2 Join Query that Maximizes Column
The clause Select TOP is not valid on DB2. Intead use SELECT * FROM myTable ORDER BY id FETCH FIRST 10 ROWS ONLY ...
SQL - How to select from mulitple possible columns names?
Very much off the top of my head: SELECT M1.MESSAGE_ID, M1.MESSAGE_NAME, M1.CREATED_DATETIME, M1.MESSAGE_SIZE, EV.EXTRACT_VALUE, M2.PATH FROM MBX_MESSAGE MI LEFT JOIN ( SELECT MESSAGE_ID, EXTRACTABLE_COUNT AS EXTRACT_VALUE FROM MBX_EXTRACT_COUNT UNION ALL SELECT MESSAGE_ID, EXTRACTABLE_UNTIL AS EXTRACT_VALUE FROM MBX_TIL_COUNT UNION ALL SELECT MESSAGE_ID, EXTRACTABLE AS EXTRACT_VALUE FROM MBX_EXTRACTABLE ) AS EV ON...
Can you check for a DB2 license in C#?
There is not an api to access the license manager in DB2. However, you have two options: Execute the db2licm and parse the output. Analyse the nodelock file in the license directory where db2 is installed (/opt/ibm/db2/V10.5/license/nodelock) ...
db2 query in shell script doesn't run with empty results in shell
$(db2 -x "connect to $DB_NAME...) executes in a subshell, which terminates the connection when the subshell exits, so by the time you get to db2 "SELECT C.PARTNUMBER..." the connection does not exist.
SQL Date Conversion Query
You can build the date in a way that will allow you to look for a specific date range. For that, CONCAT (or || : || can be used as a synonym for CONCAT [see Note 19]) will be helpful. So you can try something like this : Select distinct...
Persisting user/db object in PHP MVC
The problem was that object variables would not persist across pages. I needed a way to do this. I have redesigned the class to make use of cookies for maintaining authentication between pages during __construct. On successful authentication, properties are loaded from the database, instead of persisted in the session....
ODBC Data Source Administrator - missing tcp/ip for installed driver
You should be able to set those properties in the Advanced settings: Add entries for the properties Hostname (use the ip address or hostname), Port (usually its 50000 for DB2) and that should be enough. Maybe you also need to set Property Protocol. Note: I have never seen a tab...
How can I write queries for the Bluemix SQL database
DB2 does not support the "AUTO_INCREMENT" statement. You can use the "GENERATED ALWAYS AS IDENTITY" command instead. CREATE TABLE discounts ( id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), title VARCHAR(255) NOT NULL, expired_date DATE NOT NULL, amount DECIMAL(10,2) NULL, PRIMARY KEY (id) );...
How to add leading spaces to output column
You can use the Concat(...) Function: SELECT col1 AS MYCOL FROM table 1 UNION SELECT CONCAT(" ", col2) AS MYCOL FROM table 2...
How to add db2 user as SYSADM
If the instance configuration parameter SYSADM_GROUP is set, any member of the group specified therein will have the SYSADM authority. If the parameter is not set, members of the primary group of the instance owner user (presumably db2inst1 in your case) will have the SYSADM authority. To check the parameter...
DB2 Resource BIND Error: DSNT500I -DBS1 DSNTBCM1 RESOURCE UNAVAILABLE
REASON 00E30083 means a deadlock on TYPE 00000800 (plan) with NAME KO2PLAN. Since you cannot (re)bind a plan that is in use by an application, one might assume that OMEGAMON is running, thus preventing the bind. As an aside, in addition to "random and poorly run forums" it is often...
How to connect to DB2 SQL database with node JS?
The latest answer on this issue gives you the answer: export DYLD_LIBRARY_PATH=/Users/.../<project_folder>/node_modules/ibm_db/installer/clidriver/lib/icc node app.js You have to do this every time you enter the shell, so you may as well put this in your .profile or .bash_profile....
Entity Framework Is Null, Is Not Null Using DB2
A colleague at work helped me. My map was wrong. The field idAtividade is nullable. The relation need be optional. this.HasRequired (T => t.ATIVIDADE) .WithMany (T => t.PERFIL_ALERTAs) .HasForeignKey (T => t.IdAtividade); correction this.HasOptional (T => t.ATIVIDADE) .WithMany (T => t.PERFIL_ALERTAs) .HasForeignKey (T =>...
Running a scheduler on more than one machine
DB2 (and most enterprise database vendors) have proper locking mechanism in place for highly concurrent workloads to satisfy the database ACID properties. You should not need to worry about placing explicit locks on your tables yourself. In DB2, an isolation level determines how data is locked or isolated from other...
Cannot initialise HikariCP pooled connection, Failure in loading native library db2jcct2
This question is equivalent to Why is DB2 Type 4 JDBC Driver looking for native library db2jcct2? If you were configuring the DataSource in code you would need to do this: // Assuming dataSource is a com.ibm.db2.jcc.DB2SimpleDataSource dataSource.setDriverType(4); DataSources are javabeans. The convention of javabeans is that a pair of...
Synchronize some tables from two databases in different DBMS
You can adapt some tools to perform what you want: Have a master table (MDM) in a database and have a replication or federation in the other databases. (MDM software, or equivalent) Create a trigger in DB2 that calls a external UDF that writes the values in MySQL. Probably, you...
sum NULL or converting NULL to 0 to sum sql db2.iseries
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 INV01P WHERE I01SUPS = '0' AND I01SUPP = '0') AS L1 ON IM.IPROD = L1.I01PROD LEFT OUTER JOIN (SELECT WPROD, SUM(WOPB-WISS+WADJ+WRCT) AS NONAV...
How do I use ROW_NUMBER in DB2 10 on z/OS?
In some DB2 versions you must use a correlation name for a subselect, as suggested by the error message: select FOO from ( select FOO from BAR ) as T Here "T" is the correlation name....
Db2 sql for partition by range select
You are mixing up two different things: table partitioning, which is a physical characteristic of a table, and OLAP (window) functions, which provide logical grouping of records in a query. I guess what you wanted was something like Select a.*, max(a.bloo) over ( partition by a.bloo ) as maxmax from...
Select most recent records by DB2 date in form of YYYYMMDD
It would be better to do this on the DB2 side, to reduce the number of records brought over. Additionally, it's better from a performance standpoint to convert the static date into a numeric date and compare to the column in your table. Rather than convert the numeric date in...
See actual code of function in DB2 for IBM i
db2,ibm-midrange,dbvisualizer,jt400
Try IBM i Navigator for web. If it is configured on your machine, you can reach it through this URL: https://your.ibm.i:2005/ibm/console/login.do?action=secure If it is not configured, then perhaps you can ask the admin to install the Windows client. It is part of Client Access for Windows and is called IBM...
Convert varchar to time datatype in DB2
After lots for hit and trial following works for me. Posting this if anyone needs this just in case. I will accept the better answer than this is anyone have: SELECT case when length((substr(created_time, LOCATE(' ', created_time)))) = 12 then time(trim(substr(created_time, LOCATE(' ', created_time),10))) else time(trim(substr(created_time, LOCATE(' ', created_time),9))) END...
Liquibase generateChangeLog command - generating changelog with insert statements
Jens is right in the comment. Liquibase has no way of determining dependencies because the main use case is tracking ran changeSets. GenerateChangeLog is a useful capability but it is not intended to handle all cases and managing dependencies is a complex task that is definitely out of scope. My...
db2 - export into File
The default behavior of DB2's EXPORT utility is to format any DECIMAL value with a leading positive/negative sign and pad it with leading zeros. To override this behavior, specify the DECPLUSBLANK and STRIPLZEROS options in your MODIFIED BY clause, or CAST the DECIMAL values to some other type in your...
How to implement paging mechanism in generic way for SQL standards?
As far as I know there is no generic functionality to implement the pagining mechanism for the all the database. The syntax to implement the pagination may also change with the database, so it is hard to say that there is a genric functionality to implement it across all the...
DB2, when trying to calculate difference between provided and stored timestamp I get an error 'The invocation of function is ambiquious'
There are several overloaded versions of the DAYS() function, accepting parameters with different data types: DATE, TIMESTAMP, and VARCHAR. When you use an untyped parameter marker (DAYS(?)) the query compiler is unable to determine which version of the function to use in the query. You can specify the parameter data...
Read in a file that contains multimple “CLOB” formatted data fields
You will need to find a character that is never part of your data fields. Let's suppose the caret symbol is never there, so you can use it as the field delimiter. You will need to place it around each data field, then try importing it specifying the following modifiers:...
Connecting Android application to BlueMix stored DB2 Database
android,database,db2,database-connection,bluemix
You can not talk directly to the SQL DB on Bluemix from your android app. You will need to create an intermediary application on bluemix that talks to the db and your android app can talk to that application. You can implement this backend as a RESTful API (Java JAX-RS,...
Creating views in as400 db2
Neither the ORDER BY nor FETCH FIRST clauses are supported in the definition of the outer fullselect in a view in DB2 for i. You can however, use a common table expression in currently supported releases of DB2 for i (6.1, 7.1, 7.2). The following was tested on 7.1: create...
Need help in SQL Query
You cannot avoid the UNION because you still have to access both TABLE_PROB and TABLE_PROB1. Depending on your DB2 version, platform, and the system configuration this might perform a bit better: SELECT TABLE1.COLS,TBLE2.COLS,TABLE3.COLS FROM TABLE1,TABLE2,TABLE3 WHERE OTHER_CLAUSE AND EXISTS ( SELECT 1 FROM TABLE_PROB WHERE COL=TABLE1.COL UNION SELECT 1 FROM...
NoSQL, ElasticSearch or even old school relational database?
From what you say it isn't clear if you attempted to tune your existing database. If you did not, it is usually much more efficient (time- and effort-wise) to tune an existing solution rather than move all your data to a new platform (which will still require tuning). Only after...
Multiple row count from single table
The group by syntax breaks the table up into groups, and allows you to perform aggregate functions (count, in your case) on each one separately: SELECT record, COUNT(*) FROM schema.table GROUP BY record ...
IBM Rational System Architect using DB2
DB2 is also product of IBM. The generic install should contain the necessary JARs to connect to DB2: Directory of C:\Program Files\IBM\SDP\runtimes\base_v61\deploytool\itp\plugins\ com.ibm.datatools.db2_1.0.3.v200805071403\driver 2012-11-02 20:31 1,973,658 db2jcc.jar 2012-11-02 20:31 2,068 db2jcc_license_cisuz.jar 2012-11-02 20:31 1,015 db2jcc_license_cu.jar 3 File(s) 1,976,741 bytes ...
Syntax error when creating stored procedure in DB2
sql,stored-procedures,cursor,db2
You have your SET statement in the middle of your DECLARE CURSOR statement. It should look like: ... DECLARE v_party_id BIGINT; -- This doesn't execute the statement, just declares the cursor. DECLARE c_result CURSOR WITH RETURN FOR select cm.contact_method_id, cm.contact_method_type_id, cm.electronic_address from core.party_contact_method pcm join core.contact_method cm on cm.contact_method_id =...
Hibernate isolation levels vs database isolation levels
Hibernate acts as an abstraction layer across databases; it would be preferable to use Hibernate's isolation level if you wanted to specify the same isolation levels for multiple supported databases (to the extent that the databases support that isolation level; for instance, Oracle won't implement uncommitted reads). Hibernate can specify...
How to access a Row Type within an Array Type in DB2 SQL PL
arrays,stored-procedures,types,db2,row
You need to declare a temporary variable of the row type and assign array elements to it in a loop: CREATE OR REPLACE PROCEDURE TEST_ARRAY (IN P_ACCT_ARR ACCT_ARR) P1: BEGIN DECLARE i INTEGER; DECLARE v_acct acct; SET i = 1; WHILE i < CARDINALITY(p_acct_arr) DO SET v_acct = p_acct_arr[i]; CALL...
How to connect Db2 Federated Views with Hibernate 4
We can connect DB2 Federated Views with Hibernate mapping. I solved the above issue by just removing the "hibernate.hbm2ddl.auto" from the configuration file and Hibernate automatically detected the Federated View tables.
1 Working Day Back In SQL DB2
select * from mytable where mydate = current date - (case when dayofweek(current date) = 1 then 2 -- sonntag when dayofweek(current date) = 2 then 3 -- montag else 1 end) days ...
Quantity of transaction logs used per application/connection in DB2
Logs are used by transactions (units of work), not connections, so -- something like this, may be? select application_handle, uow_log_space_used from sysproc.mon_get_unit_of_work(null,null) order by 2 desc fetch first 5 rows only ...
Migration of XML functionality from Oracle to DB2 LUW (ver 9.7 on AIX)
xml,oracle,plsql,db2,procedure
The XMLTABLE function in SQL/XML is capable of shredding an XML document into a multi-row relational expression without requiring a temporary table. To pull a single value out of an XML document, you may decide to use the XMLQUERY function instead. Check out some of IBM's examples in the DB2...
How to connect PHP web service on BlueMix and Android app? [closed]
Yup. In the Bluemix console, click on your application, bind existing service and then select the sqldb service. It will then prompt you to restage. After the restage, you can read the VCAP_SERVICES environment variable in your php code to get the database credentials. This thread has detailed steps...
IBM Data Studio can't browse data on SAMPLE (DB2 Express-C)
Your issue is that you are logging in to the command line as Nenad (you can tell that by the default schema), and that you are logging into the DB through DataStudio as db2admin. You either need to log in to Data Studio as Nenad. Or as Nenad you need...
Include “0” counts in result set, DB2
Here's a simple solution using a recursive CTE that generates values from 1 to 10. Once you have that series, left-join your original query to it, and you're all set: WITH v (column_1) AS ( SELECT 1 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT column_1 + 1 FROM v WHERE column_1 <=...
How to create boolean variable in a stored procedure using db2?
Try to specify separator and change BOOLEAN to SMALLINT This is example: --/ CREATE PROCEDURE deleteNotActualData() SPECIFIC proc_vars LANGUAGE SQL BEGIN DECLARE trueOrFalse SMALLINT; END / ...
Java mapping for COBOL comp and comp-3 fields
COBOL does not have strings, it has fixed-length fields with no terminators. So for FIELD-1 you have three "characters". A PICture of X can allow any of the 256 possible bit-values, but would typically contain human-readable values. FIELD-2 is a binary field. You could consider it to be a four-byte...
db2 not able to load output of uniq command in linux
Trim off any leading whitespace before passing the variables into the DB2 CLP. Until you confirm everything is working properly, leave the verbose option enabled on the DB2 CLP. INN=`echo $INN` ID=`echo $ID` db2 -v "INSERT INTO data( inn, id ) VALUES('$INN', '$ID')" ...
DB2 Select from two tables when one table requires sum
You do this using a join to bring the tables together and group by to aggregate the results of the join: select s.ProductId, s.SupplyStock, sum(d.DemandStock), (s.SupplyStock - sum(d.DemandStock)) as Available from Supply s left join Demand d on s.ProductId = d.ProductId where s.ProductId = 109 group by s.ProductId, s.SupplyStock; ...
How to “combine” arrays
Here is my suggestion. $array1 is the first array and $array2 the second. You cycle through them and if empnum matches you swap related values. This is a 'pure' PHP solution. foreach($array1 as $key1 => $data1) { foreach($array2 as $key2 => $data2) { if ($data1['pEmpNum'] == $data2['empNum']) { $data2['unitRate'] =...
published reports don't work - Database logon failed Error
c#,iis,visual-studio-2013,crystal-reports,db2
In my case the problem was that on IIS Application Pools/ my web app/ Advanced Settings the 32 bit Applications was disable and I wasn't including on my project the folder aspnet_client where crystal run-times files are storage, so the time I was publishing the app, those files wren't transferred....
Using @ on Variable Names
The @ character is simply the first character of the SQL identifier [variable name] naming the parameter defined for the arguments of the User Defined Function (UDF); slightly reformatted [because at first glance I thought that revision might make the at-symbols appear more conspicuously to be part of the name,...
Drop DB2 database if exist
There is not a command or language in DB2 that support that instruction. You have to define what to do when the given name exist as an alias of a database (not the real name of the database), drop the alias? rename it? The script will look like: List the...
JPA in Java SE vs Java EE performance
java,java-ee,jpa,db2,eclipselink
Finally, I figured it out: in JEE enviroment JPA used connection pooling automatically; but in JSE enviroment, no connection pooling was available when a connection to any db2 database created, a number of agents are started (e.g. monitoring, collection statistics, etc.); to start (and terminate) these agents it took about...
Create relation one-to-many without Foreign Key in nhibernate4
c#,nhibernate,db2,nhibernate-mapping,nhibernate-4
That error doesn't seems to be connected to problems with the foreign keys. It seems to be that the NHibernate can't find the XML files. The "common" problems are normally three: The XML files must be set (in their Properties) with the Build Action = Embedded Resource In the configuration...
Db2 .SqlIntegrityConstraintViolationException: SQLCODE=-803, SQLSTATE=23505
Check if there is really the same integrity constraint in your source database (Oracle). Otherwise you might import rows which exist in your source table (because there isn't a constraint), but which can't be imported in the target table. Check if the column indices are really the same in...
Is it possible to activate the compatibility mode for Oracle in SQL Database?
Oracle compatibility is a database level feature and cannot be enabled if the database is created with the feature disabled.
sql convert from “record per month” to “record from/until”
This would be easier if your db stored a full date instead of just the year/month (or at least an equivalent combined type). Or if you could operate over the original base data: SELECT emp, partTime, MIN(monthStart) AS monthStart, MAX(monthNext) AS monthEnd FROM (SELECT emp, partTime, DATEADD(month, month - 1,...
Load period-separated text file into db2
When using delimited files, the standard DB2 import and load utilities do not have the ability to specify a row record terminator. The LF character (or CRLF on Windows) is the only record terminator you can use. So, you would need to pre-process your file (to either replace each period...
join 2 tables and get some columns from 1st table and max timestamp value from second table
Do a LEFT JOIN with a GROUP BY to find the MAX(timestamp): select e.empid, e.empname, e.status, max(timestamp) as lastusedts from employee e left join facilities f on e.empid = f.empid where e.status = 'active' group by e.empid, e.empname, e.status Alternatively, a correlated sub-select for the max-timestamp: select e.empid, e.empname, e.status,...
Merge Query in DB2
Try this instead, I think you forgot your identifier in your select statement because after the "ON" statement "CORRECT.Primary_ID" doesn't associate to anything. MERGE INTO TABLE_1 as O USING ( SELECT ((TO_NUMBER(TABLE_3.Total_Whsle_Price)-TO_NUMBER (TABLE_2.OPT_BASE_WHSLE)) - ((TO_NUMBER(TABLE_3.Total_Whsle_Price) -TO_NUMBER(TABLE_2.OPT_BASE_WHSLE))*TO_NUMBER (TABLE_1.AUC_MILEAGE))/100000 ) as CORRECT_FLOOR_PRICE, TABLE_1.Primary_ID AS Primary_ID FROM TABLE_1, TABLE_2,TABLE_3 WHERE TABLE_2.Primary_ID = TABLE_1.Primary_ID...
Getting the multiple count() in a sinle select statement without script repitiion
sql,database,db2,correlated-subquery
Something like: select CHH.ADD_DATE, CHH.TRANSACTION_TYPE, count(1) from HW_PROD.ILCCHH CHH 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 = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE IN ('PR','SR','EX','01','33') AND CHH.ADD_DATE IN (CURRENT_DATE, CURRENT_DATE - 1 DAYS) AND <remaining where clause> GROUP BY CHH.ADD_DATE, CHH.TRANSACTION_TYPE Now it...
Trimming Blank Spaces in Char Column in DB2
Use LTRIM() or TRIM() to trim blanks before the LEFT() select pat.f1, hos.hpid, hos.hpcd from patall3 pat join hospidl1 hos on pat.f1=hos.hpacct where TRANSLATE( LEFT( LTRIM(hos.hpid), 3 ), 'AAAAAAAAAAAAAAAAAAAAAAAAA', 'BCDEFGHIJKLMNOPQRSTUVWXYZ' ) <> 'AAA' order by pat.f1; Note that the use of such functions in the WHERE clause means that performance...
Getting “failed to start accepting connection” while deploying my app into bluemix
There are a few syntactical errors in your snippet (missing closing brackets etc). Try using this instead: var express = require('express'); app = express(); var ibmbluemix = require('ibmbluemix') var ibmdb = require('ibm_db'); var http = require('http'); var url = require('url'); var logger = ibmbluemix.getLogger(); var PORT = (process.env.VCAP_APP_PORT || 8000);...
Executing DDL in compound SQL using DashDB (DB2)
You need to use dynamic SQL to execute some DDL statements: EXECUTE IMMEDIATE 'CREATE TABLE test AS (SELECT...' ...
Append data to a DB2 blob
If you only need to append data to the end of a BLOB column and don't want to read the entire value into your program, a simple UPDATE statement will be faster and more straightforward. Your Java program could run something like this via executeUpdate(): UPDATE file_storage SET data =...
Recursive Query for Date Range
A bit of playing with this in perl gives me: #!/opt/myperl/5.20.2/bin/perl use 5.10.0; use DBI; use DBD::DB2; use Data::Dump; my $sql = <<EOSQL; WITH DATE_RANGE(DATE_FOR_SHIFT) AS (SELECT CAST(? AS DATE) FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT DATE_FOR_SHIFT + 1 DAY FROM DATE_RANGE WHERE DATE_FOR_SHIFT < CAST(? AS DATE)) SELECT DATE_FOR_SHIFT FROM...
DB2 Z/OS 10 Equivalent of AUTONOMOUS procedure
You could simulate (Actually, this is the way autonomous SPs works in LUW) the autonomous option by calling an external stored procedure (in C or Java) that creates another connection to the database and call the "autonomous" SP. By recreating the connection from an external SP, you will have the...
My sql statement has two parts attached with 'union all'. I want the second part to exceecute only when some condition is true
What about this: SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' AND @Combo = true ORDER BY City; It still runs the second part of the union however will only return records if @Combo is true. Saves lots of IF THEN ELSE...
Java, passing database connection into different classes.
java,database,db2,database-connection
how is that "conDA" object name can be understood as a connection of database? because the class ConnectionDA have the method getConnection implement that returns an object of the type connection. public Connection3(ConnectionDA conDA) throws ClassNotFoundException { this.conDA = new ConnectionDA(conDA.getConnection()); } And here the implementation of the getConnection()...
SQL Having statement with Date Range - The grouping is inconsistent
Based on your constraints it should work fine with a where clause. The intention of the having clause is to compare to some aggregate. Obviously you aren't doing that so should read as follows: AND OS.CSHR_SYSUSR_ID = CSH.CSHR_SYSUSR_ID WHERE OS.STR_NBR = '0121' AND CSH.SLS_DT BETWEEN '2015-01-01' AND '2015-06-16' GROUP BY...