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.

 

 
Extracting files from IBM OnDemand 7 Server

I have around 30 tables in IBM Ondemand 7.1.0.7 running on AIX against Db2 with reports that need to be converted to files, these files were put in there i guess about 15 years ago and there is little to no documentation.

From about 5 tables of them I manage to do so via:

 arsadmin retrieve -g $1 -h $ARS_SRVR -n $2 -u $USERID $PASSWD $3 arsadmin decompress -c O -s $3 -o $4

The compression is as seen in the ARSAG>ARSSEG>Filetables always O. This works perfect. The other ones seem to have been stored in a different way:

For the other ones the final output consists of an unknown file format full of @@@@@'s. So I am not sure if the decompression needs to be different or if the file format is not at all readable but unknown. I would expect regular text output since the "gui" also shows plain text output. And since it deals with a gazillion files it's undoable to extract these by hand.

I also tried to use the offsets:

arsadmin retrieve -h XXX -u XXXX -p XXX -n 3-0 -g XXX 1FAAAarsadmin decompress -c O -s 1FAAA -o 1FAAA_D -b 0 -l 103908

But this delivers me the same unreadable file, further splitting etc would be no use since the output is unreadable.

So anyone has worked with this before and has a clue on if either the command should be different or what the output is and how it should be further processed to retain a readable textual output? The magic bytes of the output seems to start with F1 E2 C5 E5 D3 and the output contains primarily @ signs (hex 40). My first guess based on the ondemand redbook and ondemand forum was that it was AFP format but an AFP viewer reports it is definitely not AFP format (but still in doubt).

An alternative route delivers the same kind of file:

arsdoc get -h SERVER -u XXX -p XXX -f "ZZZ" -i"wherex=y" -o test.out -c

However different AFP viewers claim this is not an AFP file, so chance is that the output needs an extra step somewhere in the chain to become readable text.

UPDATE:

Solved, I will leave it here just so if anyone ever needs it, it can be found. The reports were apparently uploaded using different codepages. This batch was in EBDIC format.

Copying from one PF in a lib to another PF in a lib and making it a CSV?

I don't think the OS will copy from one PF to another PF using comma delimiters so does anyone have a utility out there that will do that?

I don't want to use the IFS just PF's in libraries.

how to convert row in column in sql

i have table employee

+------+-----------+| name | is_active |+------+-----------+| a | 0 || b | 1 || c | 1 || d | 1 || e | 0 |+------+-----------+

when we use query

select is_active, count(*) count from employee group by is_active;

then output in form of

+-----------+--------+| is_active | count |+-----------+--------+| 1 | 3 || 0 | 2 |+-----------+--------+

but i want to output in that form

+-----------+---+----+| is_active | 0 | 1 |+-----------+---+----+| count | 2 | 3 |+-----------+---+----+
db2set DB2_COMPATIBILITY_VECTOR=

I want to disable all compatibility features by resetting the DB2_COMPATIBILITY_VECTOR registry variable, so i used following command:

db2set DB2_COMPATIBILITY_VECTOR=

db2stop

db2start

but it is not effected to regester variable.

Therefore I edit the DB2_COMPATIBILITY_VECTOR dirrectly bu regedit admin role, but when I restart computer, the setted value back to default value (MYS).

Please give me any suggestion if you had same problem.

Thank you very much!

Library List is not being used in remote PHP DB2 connection to IBM i

I've successfully connected to a remote IBM i DB2 database (AS400) from my local Windows PC via PHP. I'm using the IBM Data Server Client in conjunction with the db2_* functions in PHP. The problem I'm having is that despite my library list being set properly, it is not being used for unqualified table names. Instead it uses the current user name as the library. However, when I qualify the table names everything works like a charm.

I've confirmed that my library list is actually changing when I create the connection by querying QSYS2.LIBRARY_LIST_INFO.

$database = '';$user = '';$password = ';$options['i5_naming'] = DB2_I5_NAMING_ON;$options['autocommit'] = DB2_AUTOCOMMIT_OFF;$options['i5_libl'] = 'MYLIB YOURLIB ANYLIB';$conn = db2_connect($database, $user, $password, $options);if ($conn) { echo "Connection succeeded."; //It succeeds}else { echo db2_conn_error()." | ".db2_conn_errormsg()."
"; echo "Connection failed.";}$sql = "SELECT * FROM QSYS2.LIBRARY_LIST_INFO"; //Works and proves my library list reflects //what I passed in when creating the connection.//$sql = "SELECT * FROM LIBRARY_LIST_INFO";//Generates: "42S02 : [IBM][CLI Driver][AS] SQL0204N ".LIBRARY_LIST_INFO" is an undefined name. SQLSTATE=42704 SQLCODE=-204"//where is the username used to connect to the DB.//It should be using the library list specified when creating the connection though. //This holds true for any table from any library including those specified //when creating the connection (which includes QSYS2).$stmt = db2_prepare($conn, $sql);$result = db2_execute($stmt);if($result){ while($row = db2_fetch_assoc($stmt)){ echo "
"; var_dump($row); //In addition to entries for QSYS, QSYS2, QUSRSYS and QHLPSYS I get entries for MYLIB, YOURLIB and ANYLIB. echo "
"; }}else{ echo "failed
"; echo db2_stmt_error()." : ".db2_stmt_errormsg()."
";}

Has anyone ever run into this while enabling i5_naming when connecting to a remote DB2 server? I'm not really sure why it wouldn't be using my library list as the PHP manual states "Unqualified files are resolved using the library list for the job." when enabled. http://php.net/manual/en/function.db2-connect.php

Alter table max length in sql not working

I am trying to alter my table ADDRESS for column ADDRESS_TYPE from VARCHAR(10) to VARCHAR(50).

I tried

ALTER TABLE ADDRESS ALTER ADDRESS_TYPE VARCHAR(50);ALTER TABLE ADDRESS MODIFY ADDRESS_TYPE VARCHAR(50);ALTER TABLE ADDRESS ALTER COLUMN ADDRESS_TYPE VARCHAR(50);ALTER TABLE ADDRESS CHANGE ADDRESS_TYPE VARCHAR(50);

none of them work and i got a DB2 SQL error:

SQLCODE=-104, SQLSTATE=42601.

This table is part of constraint in a few other tables. Trying to get the correct ALTER statement to modify the max length.

Getting wrong output when using IN keyword db2

so this is my query :

select count(a.rs1) as partsProduced, a.DEVICEID ,a.DATA_MAINPROGRAMNO from ( SELECT currow, rs1, rs2,DEVICEID,DATA_MAINPROGRAMNO FROM ( SELECT current_row.row1 currow, current_row.DATA_RUNSTATUS rs1, current_row.DEVICEID DEVICEID, current_row.DATA_MAINPROGRAMNO DATA_MAINPROGRAMNO, next_row.row1 nextrow, next_row.DATA_RUNSTATUS rs2 FROM (SELECT rownumber() over(order by DATA_TIMEINMILLISECS) as row1, m.* FROM "IOTP_SL2IID_GOVHISTORICALDB_2017-03-20" m where DEVICEID ='deviceName' ) AS current_row LEFT JOIN (SELECT rownumber() over(order by DATA_TIMEINMILLISECS) as row1, m.* FROM "IOTP_SL2IID_GOVHISTORICALDB_2017-03-20" m where DEVICEID ='deviceName' ) AS next_row ON (current_row.row1 = next_row.row1 - 1) where (TIMESTAMP_FORMAT(current_row.DATA_TIMESTAMP,'YYYY.MM.DD.HH24.MI.SS') BETWEEN '2017-03-20 14:00:00' AND '2017-03-20 15:00:00')) AS a WHERE a.rs1 = 3 AND a.rs2 = 0) as a group by a.DEVICEID ,a.DATA_MAINPROGRAMNO;

I'm getting correct output in this where parts produced is 17 for that given hour, but whenever I replace DEVICEID= 'deviceName' to

DEVICEID IN ( select d.DEVICE_ID from device d left join line l on ( l.id=d.LINE_ID ) where l.id=1 

as I need multiple deviceNames and fire a query with that, now I'm getting 300 something for the same device which I ran earlier with single device, so am I using the IN keyword correct?? or is there any editing to do?

My NodeJS app in internal bluemix can't access an external DB2 but inside IBM network

I'm trying to get some data from an DB2 outside the bluemix but inside IBM network with my node application. But seems that my app can't "see" the database.. I'm getting an error of can't locate remote database... Does anyone have an insight of how can I solve this?

Compiling class in Memory and storing them in Database in Java

I want to compile a class (for example Test1) in memory and directly store the byte code in a database as a BLOB.

Additionally I also want to compile a new class (Test2) which calls Test1 which is stored in the database. So I need to load this class from the database to compile Test2. Is that even possible, and if yes how could I do that?

I have read about the InMemoryCompiler and the DatabaseClassLoader, but I'm not sure if thats the right thing to work with.

https://github.com/trung/InMemoryJavaCompiler

How can I load java class from database?

If someone just know how to solve the first problem, that would also be great.

Thank you for your answers.

Combine tables with different data

I have two tables:

+--------+-----+| name | A | +--------+-----+| abc | 10|| def | 17|| ghi | 27|+--------+-----++--------+-----+| name | B | +--------+-----+| abc | 9 || def | 55|| xyz | 92|+--------+-----+

Now I need to combine these two tables, that each name appears only once, but it has the result of A and B in it. If there is no matching entry than a zero should be displayed.

So the result should look like this:

+--------+-----+-----+| name | A | B | +--------+-----+-----+| abc | 10| 9 || def | 17| 55|| ghi | 27| 0 || xyz | 0 | 92|+--------+-----+-----+

Later I might have a third table with C which should be added in the same way.

How would the SQL look like?