I used the below statement to generate the hashed value for the table.
select ACTNO,HASH_SHA1(concat(ACTNO,ACTKWD,ACTDESC)) from ACT order by ACTNO
where ACTNO(PRIMARY KEY) - SMALLINT, ACTKWD - CHAR, ACTDESC - VARCHAR
But when I tried to execute this statement am getting an error.
Error:ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/NT64] SQL0440N No authorized routine named "CONCAT" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884\r SQLCODE=-440
Can someone please help me to solve this error
Current versions of Db2-LUW offer a function named HASH. To use the SHA1 algorithm, you specify argument 1 for the algorithm parameter. You should consider a stronger algorithm.
The CONCAT() function accepts only two arguments, but you give it three, so Db2 will throw an exception ( no such function). Use instead the concatenation operator ||, after casting non character columns to varchar.
If your ACTKWD and ACTDESC columns allow NULL, use COALESCE().
Example for Db2-LUW:
select actno, hash(varchar(actno)||coalesce(actkwd,'')||coalesce(actdesc,''),1) from act