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.

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.

ERRORCODE=-4461, SQLSTATE=42815 in DB2

I have exported some data from schema A (table x)to XML and I am reading the XML and inserting the data into schema B(table y). while inserting the data after 20000 records it says

com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][t4][20111][11366][3.63.75] The value of a host variable is too large for its corresponding use. Host variable=1. ERRORCODE=-4461, SQLSTATE=42815com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][3.63.75] Batch failure.

The batch was submitted, but at least one exception occurred on an individual member of the batch.

I compared the data types of the corresponding columns in table x and table y they are the same. It is BIGINT for the identity(Auto increment) col and LONG VARCHAR in both source and destination..

Kindly help in resolving this issue.

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?

Merging multiple tables sharing a column

I have a list of tables like this:

t1

ID | Name3 | 'AAA'4 | 'BBB'5 | 'CCC'6 | 'DDD'7 | 'EEE'

t2

ID | Password3 | 'test'6 | 'password'

t3

ID | Birth Year | Last Name4 | 1990 | 'John'6 | 1988 | 'Megan'7 | - | 'Bob'

t4

ID | Birth Year7 | 1985

I want to merge them all into this, noticing that t3 and t4 both have birth year columns, but the value will only be in either one.

ID | Name | Password | Birth Year | Last Name3 | 'AAA' | 'test' | - | -4 | 'BBB' | - | 1990 | 'John'5 | 'CCC' | - | - | -6 | 'DDD' |'password'| 1988 | 'Megan'7 | 'EEE' | - | 1985 | 'Bob'

Does anyone know how this can be done? t1 is the "master" table, so it will always contain all the IDs.

I've tried:

select * \from t1 \LEFT outer join t2 on t1.ID = t2.ID \LEFT outer join t3 on t1.ID = t3.ID \LEFT outer join t4 on t1.ID = t4.ID 

But it doesnt work properly, it has separate columns for each individual columns in t1, t2, t3, t4

cooperl22/laravel-db2 package in laravel 4.2

I have a laravel 4.2 (upgraded from 4.1) app that I would to replace the ccovey DB2/ODBCdriver with the cooperl22/laravel-db2 package. I can use composer to install version 1.0 okaybut when I try to use it I get the error "ReflectionException: Class db does not exist".

The full error from artisan is:{"error":{"type":"ReflectionException","message":"Class db does not exist","file":"/var/www/laravel/vendor/laravel/framework/src/Illuminate/Container/Container.php","line":504}}

in my composer.json I have:"cooperl/laravel-db2": "1.0.*"

I added 'Cooperl\Database\DB2\DB2ServiceProvider', to my app.php file

and my controller is making a very simple query:DB::connection('iseries')->table('my_users')->first();

my database.php file looks like:

'asw_odbc' => [ 'driver' => 'odbc', 'host' => env('DB2_HOST', '10.10.2.20'), 'database' => env('DB2_DATABASE', 'S10F3EC1'), 'username' => env('DB2_USERNAME', ''), 'password' => env('DB2_PASSWORD', ''), 'charset' => 'utf8', 'ccsid' => 1208, 'prefix' => '', 'schema' => env('DB2_DEFAULT_SCHEMA', 'GB1555AFGD'), 'i5_libl' => '', 'i5_lib' => '', 'i5_commit' => 0, 'i5_naming' => 0, 'i5_date_fmt' => 5, 'i5_date_sep' => 0, 'i5_decimal_sep' => 0, 'i5_time_fmt' => 0, 'i5_time_sep' => 0, 'options' => [ PDO::ATTR_CASE => PDO::CASE_LOWER, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_PERSISTENT => false ]],

How can a troubleshoot this further?

Custom filter in DataConnect

As you may have realized, I am using a lot this IBM Bluemix DataConnect service. When creating an activity, you can refine the data you are going to export, using a custom filter (it enables a WHERE clause to add logic to the query)

I am trying to filter a TIMESTAMP field, trying to get 1 month old data, but the sentence is not working.

DATE(SHIPMENT_TMS) >= CURRENT DATE - 2 MONTH - (DAY(CURRENT DATE)-1)DAYS

I get the following error message

Custom Filter failed. Retry the shaping operation you just performed. *Filter rows by using the SQL WHERE clause expression 'DATE(SHIPMENT_TMS) >= CURRENT DATE - 2 MONTH - (DAY(CURRENT DATE)-1)DAYS' java.lang.RuntimeException: [1.36] failure: end of input expected DATE(SHIPMENT_TMS) >= CURRENT DATE - 2 MONTH - (DAY(CURRENT DATE)-1)DAYS

It may be that DATE() or DAY() are not recognized by the service. Did anyone attempt to do such filter?

Thanks

Connect to DB2 database in eclipse via jdbc

I'm trying to connect to an IBM DB2 database with Eclipse (version Juno) via JDBC.I've added the drivers (external jar files) to my project and the driver is loaded correctly ...

public static void main(String[] args) throws SQLException, ClassNotFoundException { Class.forName("com.ibm.db2.jcc.DB2Driver"); System.out.println("Driver loaded"); Connection dbConn = DriverManager.getConnection("jdbc:db2://***.**.***.*:50000/BWUEBDB", "username", "password"); System.out.println("Connected");}

I also know that the connection data (database path, username, password) is correct.But I get a java.lang.NoClassDefFoundError:

Exception in thread "main" java.lang.NoClassDefFoundError: sun/io/UnknownCharacterException at com.ibm.db2.jcc.b.a.(a.java:238) at com.ibm.db2.jcc.b.b.a(b.java:1624) at com.ibm.db2.jcc.c.p.a(p.java:350) at com.ibm.db2.jcc.c.p.(p.java:404) at com.ibm.db2.jcc.b.b.(b.java:256) at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:163) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at AppTest.main(AppTest.java:17)Caused by: java.lang.ClassNotFoundException: sun.io.UnknownCharacterException at java.net.URLClassLoader$1.run(Unknown Source) at java.net.URLClassLoader$1.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) ... 9 more

There must be something wrong with the project properties but I really don't know where to look for.

Equivalent of LIMIT for DB2

How do you do LIMIT in DB2 for iSeries?

I have a table with more than 50,000 records and I want to return records 0 to 10,000, and records 10,000 to 20,000.

I know in SQL you write LIMIT 0,10000 at the end of the query for 0 to 10,000 and LIMIT 10000,10000 at the end of the query for 10000 to 20,000

So, how is this done in DB2? Whats the code and syntax?(full query example is appreciated)

How to update multiple columns in single update statement in DB2

I want to update multiple columns of a table in DB2 with single Update statement.

Any hint or idea will be appreciable. Thanks.