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.

 

 
How to handle DB2 Default value for date

So I have a field called an Obsolete change date in a DB2 table which stores the date when a record was marked as Obsolete. I'm not able to figure out how to define this in the create table statement.

If i give default not null, it will enter a default value which is the current date which i dont want to be populated because if it got populated, it would indicate the record was made obsolete on the current date.

Also, I dont want to populate with NULL. So how can i handle this situation?

The database returned no natively generated identity value

I am using IBM DB2 V 9.1.0.356. I am using DB2 JDBC driver version 9.7.

I am using these technologies for my application.

Spring MVC, Hibernate, DB2, Websphere

In my Create Table script; ID column is generated as:

ID BIGINT GENERATED BY DEFAULT AS IDENTITY

In Java Entity class its configured as:

@Id @GeneratedValue(strategy = GenerationType.AUTO)@Column( name = "id", nullable = false )

When I saves and object by calling this through hibernate:

*.save(persistentObject);

Data is saved. But I got following Exception:

org.hibernate.HibernateException: The database returned no natively generated identity valueat org.hibernate.id.IdentifierGeneratorFactory.getGeneratedIdentity(IdentifierGeneratorFactory.java:90)

Note: My application is configured on two servers on different machines. From one machine I can succefully save data; but from other I got above mentioned exception.

DB2: Retrieve related tablename for a column

I want to check column values of a specific table, but forgot the tablename. I only have the column names. What can I do?

I remember that I can get the tablenames from the DB2 catalog with:

select tabname from syscat.tables

What is the query to retrieve the tables related to a particular column in DB2?

How to display only the db2 query result via shell script and not the query?

There is probably a very simple solution here, but I am probably not using the right search terms. I have a sql query running in a shell script. I get the results I am looking for, however, I am also getting the sql query as part of of the result. How can I suppress this and just show the result?

My script:

#!/usr/bin/shdb2 connect to MYDB >/dev/null 2>&1;db2 -x -v "select A, B, C from MYTABLE";db2 connect reset >/dev/null 2>&1;

And my output looks like this:

select A, B, C from MYTABLEAAA BBB CCCAAA BBB CCC

I would like to get rid of the first row and just show the result. What am I missing?

Thanks in advance for your help!

COBOL DB2 sql query

I have one DB2 table. One coulmn of the table has XML query which gives information about number of forms that can be printed for a report type that can be genrated by a company.

If the user request one report type then the query should fetch number of forms that company can be printed for that report type.

If the user requests two report types then the query has to fetch sum of the forms that company can print for that report type.

Similary we can give upto five report codes as input and the query should fetch sum of all the forms that company can print for the 5 report types.

If the user don't request any report type then it has to fetch sum of all the above 5 report type forms to be printed.

I have the query for the If the user don't request any report type then it has to fetch sum of all the above 5 report type forms to be printed.

It is as below

 SELECT COALESCE( MAX( COALESCE( XMLCAST(XMLQUERY('/MasterData/DetailData/ USW2Counts/Form/Rpt820/text()' PASSING MTR.MST_TRANS_TX) AS INTEGER ),0 ) /100 + COALESCE( XMLCAST(XMLQUERY('/MasterData/DetailData/ US1099RCounts/Form/Rpt843/text()' PASSING MTR.MST_TRANS_TX) AS INTEGER ),0 ) /100 + COALESCE( XMLCAST(XMLQUERY('/MasterData/DetailData/ US1099MCounts/Form/Rpt863/text()' PASSING MTR.MST_TRANS_TX) AS INTEGER ),0 ) /100 + COALESCE( XMLCAST(XMLQUERY('/MasterData/DetailData/ PRW2Counts/Form/Rpt833/text()' PASSING MTR.MST_TRANS_TX) AS INTEGER ),0 ) /100 + COALESCE( XMLCAST(XMLQUERY('/MasterData/DetailData/ VIW2Counts/Form/RptVI3/text()' PASSING MTR.MST_TRANS_TX) AS INTEGER ),0 ) /100),0) CMP_FORM_CNT FROM DAA1.PA04.MASTER_TRANS_MTR MTR WHERE cmp_c IN ('A86','6EE','97A') and MTR.OFC_C = '29' and XMLEXISTS('/MasterData/DetailData[@DetailID="90"]' PASSING MST_TRANS_TX ) AND MTR.DATA_YR_C = '2016' ORDER BY CMP_FORM_CNT DESC 

Can any one help me to write the single query for all the above cases. No stored procedures can be used

Hive query to get the unmatched row in a table [on hold]

Need help with hive query.There are 2 identical table(copy in 2 different environment) but one of the table has 4 more rows and i need to find those 4 rows but the catch is there is no Primary key or any column which has unique value in it, neither the data is in sequence.I tried with left outer join query but no correct result.Moreover i want this in hive and MINUS and NOTIN doesn't work in hive.Any other logical solution will be appreciated.

Problems with stored procedures
create procedure SpecialPrice(IN T_ID int,Discount_P Decimal(5,3))BeginUpdate Ticketset Price=Price*Discount_Pwhere T_ID = Ticket_ID ;End@

I want to create a stored procedure that will update discounted price on the table ticket however i am having trouble creating this stored procedure and it has error and since i am running these codes with IBM Data studio the displaye these errors beside Please Help

The errors : 1)on the line BeginDB2 for Linux, UNIX, and Windows: "BeginUpdate Audienceset Price=Price*Discount_Pwhere T_ID = Ticket_ID" appears to be misplaced. 2) On the line END@ DB2 for Linux, UNIX, and Windows: Unexpected text "End@" encountered.
UDF on DB2 11.0

I was asked to build a user defined function on our Mainframe environment that checks for a search string in a longer string. The only catch is that if we search for example for 'AA' in 'ABCAADAA' the only valid result is the last AA because the first AA actually split in CA and AD.

CREATE FUNCTION F#CRE#WK (WK CHAR(02), WKATTR CHAR(10)) RETURNS INTEGER LANGUAGE SQL READS SQL DATABEGIN DECLARE INDEX INTEGER DEFAULT 1; WHILE (INDEX < 9) DO SET INDEX = LOCATE_IN_STRING(WKATTR, WK, INDEX); IF (MOD(INDEX, 2) <> 0) THEN RETURN 1; END IF; END WHILE; RETURN 0;END;

It is working fine when I implement it using Data Studio but if I put it onto the host directly (we're using Quick32770) I'm getting a bunch of errors which don't make sense at all. I couldn't find any helpful resources(searched the whole IBM page and Google of course).

First error I'm getting is:

SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ; 

Which refers to the line I'm declaring my index variable. If I remove the semicolon it tells me that the SET is illegal there because it is expecting a semicolon. I cannot think of anything else I could try(I messed around with the code a lot but errors just kept getting more weird.). I started working in this field while being in college just a couple of weeks ago and nobody here has actual knowledge about this so I was hoping to find some help here. If there's anything else you need, just let me know!

Thanks in advance.

How to Execute an SQL Anonymous Block in DB2?

I want to make a search through a schema of columns whose data includes 'ABC'. In Oracle PL/SQL, I would do it like this:

DECLARE v_cnt integer;BEGIN for i in (select tabname, colname from all_tab_cols --syscat.columns where tabschema = 'DATA' order by tabname, colname) loop execute immediate ' select count(1) from '||i.tabname||' where upper('||i.colname||') like ''%ABC%''' into v_cnt; if v_cnt > 0 then dbms_output.put_line(i.tabname||' - '||i.colname) end if; end loop;END;

However it is in an DB2 database. I have tried search online for the correct syntax, but I cannot find it. syscat.columns is the oracle equivalent for all_tab_cols, so I just need the correct syntax for an anonoymous SQL block in DB2.

Would anyone help me with the syntax, please?

Select the first row in a join of two tables in one statement

I need to select only the first row from a query that joins tables A and B. On table B exist multiple records with same name. There are not identifiers in any of the two tables. I cannot change the scheme either because I do not own the DB.

TABLE ANAMETABLE BNAMEDATA1DATA2Select Distinct A.NAME,B.DATA1,B.DATA2 From A Inner Join B on A.NAME = B.NAME

This gives me

NAME DATA1 DATA2sameName 1 2sameName 1 3otherName 5 7otherName 8 9

but I need to retrieve only one row per name

NAME DATA1 DATA2sameName 1 2otherName 5 7

I was able to do this by adding the result into a temp table with a identity column and then select the minimum id per name.

The problem here is that I require to do this in one single statement.