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.

 

 
Pivoting DB2 Data - where number of columns and and rows are not fixed

I've seen a few similar Q/A's on here, but the cases I've seen were variations on the "sales by year across quarter" variety, so DECODE is used, with 4 categories.

In my case, I don't know in advance how many rows or columns the pivot will have.

| Pay | Age | Value ||-----|-----|-------|| 1 | 1 | 10 || 1 | 2 | 20 || 1 | 3 | 30 || 2 | 1 | 90 || 2 | 2 | 80 || 2 | 3 | 70 |

and we want the result set as

PAYGROUP Millennials GenX Boomers 1 10 20 30 2 90 80 70

This would be easy with a PIVOT statement, i.e.

Transform Max(VALUE) AS VSELECT PAYGROUPFROM tableGROUP BY PAYGROUPPIVOT AGEGROUP;

but my DB2 has no PIVOT function.

The number of pay groups and age groups may vary from case to case, e.g., the data can have different numbers of pay and age groupings for different cases.

Testing DB2 Timestamp function in H2

I'm trying to make unit tests work in H2 for the DB2 function TIMESTAMP(Date, int) which sets the precision for the timestamp.

A similar question led me tohttp://www.h2database.com/html/features.html#user_defined_functionsbut I'm not sure how the overloading would work in this scenario.

Can anyone give me an example?

( Also, please don't answer just to say that having different databases for testing and production is bad practice. It's not something I'll be able to change :/ )

Unable to connect db2 database in JMeter

Getting below Response on connecting to DB2 database


"Cannot create PoolableConnectionFactory ([jcc][t4][10109][10354][3.59.81] The version of the IBM Universal JDBC driver in use is not licensed for connectivity to QDB2 databases. To connect to this server, please obtain a licensed copy of the IBM DB2 Universal Driver for JDBC and SQLJ. An appropriate license file db2jcc_license_.jar for this target platform must be installed to the application class path. Connectivity to QDB2 databases is enabled by any of the following license files: [ db2jcc_license_cisuz.jar ]. ERRORCODE=-4472, SQLSTATE=42968)"*


db2jcc.jar and db2jcc_license_cisuz.jar already added in JMeter(3.2)'s lib folder.

See myconfiguration

Is there any alternative for column range partitioner in spring Bach remote partitioning?

Just take a normal case where I am taking data from DB2 , doing some business on data and writing it into mongoDB. This I am doing with spring batch column range partition(Remote partitioning) but the problem is in my DB2 table there is no sequential column , so each partition is having different data count. Because of this load is different for each slave. My requirement is to distribute load in slaves equally.

Remove leading zeros

Given data in a column which look like this:

00001 0000026 00

I need to use SQL to remove anything after the space and all leading zeros from the values so that the final output will be:

126

How can I best do this?

Btw I'm using DB2

How to select a limited number of groups

I'm developing a report on BIRT for ControlDesk/Maximo and I need to show the most requested groups of tickets.

I got how to select a limited number of tickets by group in DB2 query, but it's not what I need. That being only the top 10 groups with most count(ticket class)

Thanks in regards

My SQL, so far, that doesn't show what I want is something like:

 select tktemplate.description as tk_description, sr.description as sr_description, sr.reportdate, sr.templateid from sr inner join tktemplate on sr.templateid = tktemplate.templateid GROUP BY sr.templateid, sr.reportdate, tktemplate.description, sr.description ORDER BY sr.templateid fetch first 10 rows only

But It only shows the overall 10 first rows, not first of each group

Invalid Cursor State Error C# DB2

I run a standard query against a DB2 database with C# ADO.NET OleDb and using statements and get System.Data.OleDb.OleDbException (0x80004005): CLI0115E Invalid cursor state. SQLSTATE=24000When I include the dispose methods for each OleDb object, the query runs. Why does this fail without the Dispose methods? From everything i have researched the using statements should dispose of the objects for me. I am using .NET 4.5.1

using (OleDbConnection conn = DBConn.BIPSConn){ using (OleDbCommand cmd = new OleDbCommand(query, conn)) { using (OleDbDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { string orderNumber = rdr.GetString(0).Trim(); string originCode = rdr.GetString(1).Trim(); string destinationCode = rdr.GetString(3).Trim(); Record record = new Record(orderNumber, originCode, destinationCode); RecordList.Add(record); } // for unknown reasons, without these dispose methods we get an Invalid Cursor State error rdr.Dispose(); } cmd.Dispose(); } conn.Dispose();}
SQL query in R passing two dates as parameters

I have a begin.date and end.date, I want to retrieve rows from a db2 table between these two dates.

When I run the query by hardcoding these values, it works fine.

Data = dbGetQuery(conn, paste( " Select * From tableA WHERE DATE(Administered_Date) between '01-01-2011' AND '01-31-2011' "))

But when I pass these two dates as parameters, not hardcoding them, I get an syntax error. This is my attempt so far. If anybody can tell me where I am going wrong, it will be helpful. Thanks in advance.

 library(RJDBC) i <- '0' begin.date = as.Date("2011-01-01") + as.numeric(i) + as.numeric('0') end.date = as.Date("2011-01-01") + as.numeric(i) + as.numeric('31') Data = dbGetQuery(conn, paste( " Select * From tableA WHERE DATE(Administered_Date) between '", begin.date,"'" AND '", end.date, "'" ")) 
Speed up execution of query to find sequential rows that have a changed value

My goal is to go through my dataset, compare each ITEM_NO/LOC day-by-day, and identify days where the VAL has changed from the day before. Right now, I do that by sorting, creating a column of row numbers, joining the table to itself offset by a row, and then only picking rows where VAL has changed.

Each month has about half a billion records. In total there's around 2.7 billion records. The data is stored in DB2 BLU. The table already has indices for ITEM_NO, LOC, and ARCV_DATE. I only have select access to the table.

I think the big bottleneck is the order by in the select statement given that n is so large. One idea I had was to try to do the sorting month-by-month and then union each of the months together.

Here's what I have so far:

with x as ( select ITEM_NO, LOC, ARCV_DATE, VAL, ROW_NUMBER() over (order by ITEM_NO, LOC, ARCV_DATE) as RN from MY_SCHEMA.MY_TABLE a where ARCV_DATE >= '2017-06-01' and ARCV_DATE < '2017-07-01')SELECT x.ITEM_NO, x.LOC, y.ARCV_DATE as CHANGE_DATE, y.VAL, x.VAL as OLD_VALFROM x INNER JOIN x AS y ON x.rn = y.rn + 1WHERE x.VAL <> y.VAL and x.ITEM_NO = y.ITEM_NO and x.LOC = y.LOC

What could I do to improve performance on this for such a dataset?

How to format Time Stored in DB2

I have time in DB2 for i like this:

TIME------14141212:30:1212 20 1212 12:121218 12 : : 

I want result like this:

TIME------14:14:1212:30:1212:20:1212:12:1212:18:1220:20:20

How I can achieve this in DB2 (7.1) on iSeries? Thanks.