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.

 

 
db2 How to use a variable in the select clause

How to use a variable in the select clause ?

I wish I could make the request

INSERT INTO tabLe1 (...) SELECT field, variable FROM table2;

but this request fails because of the variable

I tested this:

SELECT 1 FROM SYSIBM.SYSDUMMY1; -- works

But

DECLARE XX INTEGER; -- works SET XX=1; -- worksSELECT XX FROM SYSIBM.SYSDUMMY1; -- Does not work

How to use a variable in the DB2 select

thank you in advance for your help

DB2 ODBC password not used

I have a 32-bit DB2 ODBC DSN created with the user name and password saved. But when I try to connect to the DB I have to enter the user name and password again. Is there any way I can bypass the authentication? I have the user name and password saved in db2cli.ini file.

DB2 ODBC via PowerShell

I am trying to create the ODBC System DSN 32 bit for DB2 ODBC Driver. Executed as Administrator.

Add-OdbcDsn -Name "PROKEY1" -DriverName "IBM DB2 ODBC DRIVER - DB2COPY1" -DsnType "System" -Platform "32-bit" -SetPropertyValue @("Server=DB-Server", "Trusted_Connection=Yes", "Database=DB")
Add-OdbcDsn : Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed(Installer error code: 11).At line:1 char:1+ Add-OdbcDsn -Name "PROKEY1" -DriverName "IBM DB2 ODBC DRIVER - DB2COPY1" -DsnTyp + CategoryInfo : NotSpecified: (PROKEY1 (32-bit ODBC System DSN):Root/Microsoft/...SFT_OdbcDsnTask) [Add-OdbcDsn], CimException + FullyQualifiedErrorId : MI RESULT 0,Add-OdbcDsn

I am able to create the DSN via the GUI.

Get-OdbcDsn

Name : PROKEYDsnType : SystemPlatform : 32-bitDriverName : IBM DB2 ODBC DRIVER - DB2COPY1Attribute : {}

Casting DB2 column to accept multi lingual characters in Open query

I am using open query to retrieve data from IBM db2 to SQL server.

Below is the sample query used

select top 10 * from OpenQuery(Link server, 'Select columnName from table where column2=15' )

The columnName needs to be converted / cast in Unicode format to accept multi lingual characters. How to use casting in the inner query?

My issue is similar to that of https://www.sqlservercentral.com/forums/997384/linker-server-to-as400-db2-character-translation-problems

I want to retrieve the data in Thai and Chinese characters. I have rows of data which are to be in Thai and Chinese characters. But the data shows as garbled when I use the command which I have provided. The column type in sql server is defined as nvarchar.

Db2 z/OS alternative for ROUTINE_SCHEMA or ROUTINE_SPECIFIC_NAME global variables of Db2 LUW

ROUTINE_SCHEMA or ROUTNE_SPECIFIC_NAME global variables in Db2 LUW can be used to retrieve the schema name or specific name of the currently executing routine.

Is there an alternative for one of them in Db2 z/OS (where both global variables do not seem to exist) ?

DB2 Query to merge 2 decimal column and find max

I am having two columns in DB2 table ExpiryDate

expiry year - Type decimal and length(4,0)

expiry month - Type decimal and length (2,0)

I need to write one DB2 query to get latest expiry date for that i need to combine both filed year and month and find out latest expiry date.

Thanks in advance

SQL syntax explanation (CAST, INSERT, DATE)

This is for educational purposes but could someone please explain what the below syntax is doing?

CAST(INSERT(CHAR(DATE(DB2_RID), USA), 3, 3, '') AS CHAR(10)) AS MONTH

I have googled the functions and understand most of it but I'm just curious on the process of turning a value of 2017-12-18-13.21.04.442956 into 12/2017

The data type for DB2_RID is a timestamp.

So I understand that CAST is converting the inner value into a character of 10. I guess my confusion is how did it know it was a date and to not include the day of the month such as 12/01/2017.

How to use SQL Conditional statements in SQL

I'm working on a BIRT Reporting. What I need to do is, If the Column1 value is Approved, Copy Column 2 value to Column 3 else null

DB2 Primary-key truncation bug?

So I've searched around quite a bit and cannot seem to find an answer to this. I have two different applications that speak to the same DB2 database; one uses Hibernate, and the other uses just the native ResultSet, PreparedStatement, etc.

Let's say for example I have an object who's primary key is "ABC". The database has this keys length defined as 3. If I use the spring repositories findOne("ABC"), it returns my object. But the strange thing is if I call findOne("ABC123456"), it will still return that same database row, except the key field in the object is "ABC123456".

Now if I run a native query on the other application using a PreparedStatement, the same thing will happen, except in this case the primary key is returned correctly in the ResultSet.

Assuming this is a DB issue, but not sure what to look at. The database column is setup as a CHAR(3).

Accessing external (DSPF) fields using arrays in RPGLE free

In the old RPG III and the non-free RPGLE/RPG IV you could "rename" fields you get from either a record of a PF/LF or a record from a DSPF.

This lead to possibilities like grouping several lines of input (additional order text) into a array. So I didn't have to MOVEL or EVAL ottxt1 to the external described field x1txt1, ottxt2 to x1txt2 and so on.

I'd only had to rename the LF record and the DSPF record fields to the array-fields, read the record and shift them from the one array to the other and display my DSPF record

 H DECEDIT('0,') DATEDIT(*DMY.) dftactgrp(*no) Fsls001 cf e workstn Fordtxtl0 if e k disk D ot s 20a dim(6) D x1 s 20a dim(6) Iordtxtr I ottxt1 ot(1) I ottxt2 ot(2) I ottxt3 ot(3) I ottxt4 ot(4) I ottxt5 ot(5) I ottxt6 ot(6) Isls00101 I x1txt1 x1(1) I x1txt2 x1(2) I x1txt3 x1(3) I x1txt4 x1(4) I x1txt5 x1(5) I x1txt6 x1(6) C k$or00 klist C kfld otonbr C kfld otopos C eval otonbr = 2 C eval otopos = 2 C k$or00 chain ordtxtr C if %found(ordtxtl0) C eval x1 = ot C endif C C exfmt sls00101 C C move *on *inlr 

But is this also possible in *FREE RPGLE? And if so, how?