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.

 

 
% Modulus Operator in DB2 V.9

I'm new to querying from a DB2 but have a scenario where I need to select vendor payments that are divisible by 5 with no remainder, over 1k, and three or more payments of the identical amount.

SELECT T.VendorID, T.Amount, T.Date, ROW_NUMBER () OVER (PARTITION BY T.VendorID, T.Amount ORDER BY T.Date) as "Order"FROM TABLE TWHERE T.Amount % 5 = 0 AND T.Amount >= 1000;

So I would write this into a subquery and then just put a parameter on the main query for (Where Order >= 3), however, it appears that the % Modulus Operator is throwing off errors. It gets a little dicey for me as I don't have access to the production DB and querying the table thorough a Cognos 11 SQL pass-through. Any assistance on how I could achieve the same results?

Thank you,BStinson

How do I view the source tables of a cube?

I have access to db2.

I also have access to some Cognos Cubes via TM1 explorer.

Is it possible to find out the source tables for the cubes ?

For example, can I see what tables are used for the FST cube shown below ? and if possible even how the tables are joined etcenter image description here . ?

How to use CASE clause (DB2) to display values from a different table?

I'm working in a bank so I had to adjust the column names and information in the query to fit the external web, so if there're any weird mistakes know it is somewhat fine.

I'm trying to use the CASE clause to display data from a different table, I know this is a workaround but due to certain circumstances I'm obligated to use it, plus it is becoming interesting to figure out if there's an actual solution.

The error I'm receiving for the following query is:

"ERROR [21000] [IBM][CLI Driver][DB2] SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row."

select bank_num, branch_num, account_num, client_id,CASE WHEN exists( select * from bank.services BS where ACCS.client_id= BS.sifrur_lakoach ) THEN (select username from bank.services BS where BS.client_id = ACCS.client_id) ELSE 'NONE' END username_newfrom bank.accounts accswhere bank_num = 431 and branch_num = 170
Add Z/OS license to an installed ODBC Cli driver on Cloud

I deployed a PHP Cloud Foundry app using PHP buildpack. This buildpack has ODBC Cli extention so I can use DB2 functions and I can connect to DB2 on cloud. But I can't connect to a Z/OS DB2 database via Secure Gateway bevause I receive license error. I have to license the driver files somehow on Cloud. I have the license file. Any idea how to do this?

Copy DB2 schema across different databases and subnets

I want an overall better way to copy all the tables and their data from one production database schema and put it into a dev database schema on a different database in a different subnet using bash for unloads and loads.It is important that the schema name on the dev database is and can be different.Table structure for both schemas is the same, only the database name, schema name and data changes.

It is important that the solution requires minimum manual manipulation. Copying files across manually is acceptable but editing file contents to change data is not, unless this can be scripted to do automatically.

Currently we run a very long series of scripted exports for each table individually to ixf lobs, followed by a very long series of carefully placed scripted loads, being careful to load the data in order, parents before child.

Unload example:

export to CLIENT.ixf of ixf lobs to $LOCATION lobfile CLIENT_lobs modified by lobsinfile select * from CLIENT;

Load example:

load from CLIENT.ixf of ixf lobs from $LOCATION modified by lobsinfile replace into CLIENT statistics no copy no indexing mode autoselect allow no access check pending cascade deferred;

I have looked at db2move, but I cannot find how to specify database and schema name during load as it appears to only be supported in the unload/export.

db2look looks promising, but does this export the data too or just the table names?

Same value being inserted into table with cursor

I'm trying to dynamically count the number of null values held in a table. So far I have this:

BEGIN DECLARE STMT VARCHAR(2000); FOR v AS CRS CURSOR FOR SELECT NAME FROM SESSION.TT1 DO SET STMT = 'UPDATE SESSION.TT1 TT1 SET NULL_COUNT = ( SELECT COUNT(*) - COUNT('''||v.NAME||''') NULL_COUNT FROM Table1 ) WHERE TT1.COLUMN_NAME = '''||v.NAME||''''; EXECUTE IMMEDIATE STMT; END FOR;END

This runs fine, and does populate the temp table with data, but it updates every row with the same value (which is obviously incorrect).

Where have I gone wrong?

Note:

  • The temporary table, TT1, was generated by another procedure, it contains two columns; NAME, and NULL_COUNT. All values in the NULL_COUNT column are undefined at this point, and the NAME column contains column names retrieved from syscolumns.
  • I also tried removing the row and then inserting a new row rather than updating on the matched column name but this provides the same results.
db2 import export to same ixf but different processes

Does db2 check for file locks when running the db2 import/export command?

I have 2 processes, one runs an export to ixf, and another process that imports the ixf. Both run multiple times a day, the export process is variable time and I don't want to import the ixf if it is currently being written to.

Export results from DB2 to CSV including column names via bash

This question branches off a question already asked.

I want to make a csv file with the db2 results including column names.

EXPORT TO ...SELECT 1 as id, 'COL1', 'COL2', 'COL3' FROM sysibm.sysdummy1UNION ALL(SELECT 2 as id, COL1, COL2, COL3 FROM myTable)ORDER BY id

While this does work, I am left with an unwanted column and rows of 1 and 2's

Is there a way to do this via the db2 command or a full bash alternative without redundant columns while keeping the header at the top?

e.g.

Column 1 Column 2 Column 3 data 1 data 2 data3 ... ... ...

instead of:

1 Column 1 Column 2 Column 3 2 data 1 data 2 data3 2 ... ... ...

Change the charset of a column value of a db2 table

I am working with a DB2 database. My problem is that for a UTF8 CLOB column, I inserted a string from a java program but now I see with DBeaver that it cannot interpret some characters in the string so there is a problem in the charset used to save the value. How to extract the value of that column and save it with the correct charset in order to fix it?

Thanks!

jpa call procedure db2

Hey guys I have a procedure in DB2 and I'm trying call it using jpa hibernate, but I'm getting error.

Jpa

StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("GL.GLMAPOUT");storedProcedure.registerStoredProcedureParameter("O_ERROR", Integer.class, ParameterMode.OUT);storedProcedure.execute();gc1glcostctrmaps = (List) storedProcedure.getResultList();

output :

Hibernate: {call GL.GLMAPOUT(?)}2018-07-22 15:30:21.397 WARN 13916 --- [nio-8082-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: -4472, SQLState: null2018-07-22 15:30:21.414 ERROR 13916 --- [nio-8082-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : [jcc][10453][12710][3.68.61] Cannot get the describe information for the calling stored procedure with name GLMAPOUT and path ' SYSIBM , SYSFUN , SYSPROC , GLDEV ' ERRORCODE=-4472, SQLSTATE=null

but in the Data Studio it is OK

enter image description here