Comments (1)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 SergeRielau commented Permalink

It turns out that RID() is blocked from being used in an MPP environment.
So we need to extract the RID from the RID_BIT() function.
Note the trickery to adjust for endianess:

 
CREATE OR REPLACE FUNCTION EXTRACT_RID(arg VARCHAR(16) FOR BIT DATA) RETURNS BIGINT
SPECIFIC EXTRACT_RID
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN CASE HEX(1) WHEN '01000000' THEN ASCII(SUBSTR(arg, 1, 1))
+ ASCII(SUBSTR(arg, 2, 1)) * 256
+ ASCII(SUBSTR(arg, 3, 1)) * 65536
+ ASCII(SUBSTR(arg, 4, 1)) * 16777216
+ ASCII(SUBSTR(arg, 5, 1)) * 4294967296
+ ASCII(SUBSTR(arg, 6, 1)) * 1099511627776
+ ASCII(SUBSTR(arg, 7, 1)) * 281474976710656
+ ASCII(SUBSTR(arg, 8, 1)) * 72057594037927936
WHEN '00000001' THEN ASCII(SUBSTR(arg, 8, 1))
+ ASCII(SUBSTR(arg, 7, 1)) * 256
+ ASCII(SUBSTR(arg, 6, 1)) * 65536
+ ASCII(SUBSTR(arg, 5, 1)) * 16777216
+ ASCII(SUBSTR(arg, 4, 1)) * 4294967296
+ ASCII(SUBSTR(arg, 3, 1)) * 1099511627776
+ ASCII(SUBSTR(arg, 2, 1)) * 281474976710656
+ ASCII(SUBSTR(arg, 1, 1)) * 72057594037927936
END;
 
Procedure then needs to be adjusted like this:
 
SET stmttxt = SUBSTR(stmttxt, 1, LENGTH(stmttxt) - 2)
|| ' FROM "' || tabschema || '"."' || tabname || '" AS T'
|| ' WHERE EXTRACT_RID(RID_BIT(T)) = ?'
|| ' AND DBPARTITIONNUM(T."' || colname || '") = ? WITH UR';
 
What I do not like about this is that we'll end up with an ugly tablescan...
 
Hmmm..