BIGINT QUESTION

Database
Enthusiast

BIGINT QUESTION

I have a table
CREATE MULTISET TABLE sys_mgmt.resintqry ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
QryDate DATE FORMAT 'YY/MM/DD',
ProcID DECIMAL(5,0),
QueryID INTEGER,
UserName VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC,
TotalIOCount BIGINT,
TotalCPUTime BIGINT,
TotalSpool BIGINT,
QryRank SMALLINT,
Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
LogDate DATE FORMAT 'YY/MM/DD' DEFAULT DATE )
PRIMARY INDEX ResIntQry_NUPI ( QryDate ,QueryID ,UserName ,Flg )
INDEX ResIntQry_NUSI ( LogDate );

I have a macro
Replace MACRO SYS_MGMT.InsResIntQry( DT (DATE, DEFAULT DATE))
AS
(
/*DELETE FROM SYS_MGMT.ResIntQry
WHERE (QryDate < (DATE -180) or QryDate = :DT or LogDate=DATE );*/

LOCKING SYS_MGMT.DBQLogTbl_Hst FOR ACCESS
INSERT INTO SYS_MGMT.ResIntQry
(
QryDate,
ProcID,
QueryID,
UserName,
TotalIOCount,
TotalCPUTime,
TotalSpool,
QryRank,
Flg,
LogDate
)
SELECT
LogDate,
ProcID,
QueryID,
UserName,
TotalIOCount,
TotalCPUTime,
TotalSpoolUsage,
CPUQryRank,
'C' Flg,
CURRENT_DATE
FROM
(
SELECT
LogDate,
ProcID,
QueryID,
UserName,
TotalIOCount,
TotalCPUTime,
TotalSpoolUsage,
ROW_NUMBER() OVER(PARTITION BY LogDate ORDER BY TotalIOCount DESC) CPUQryRank
FROM
(
SELECT
LogDate,
ProcID,
QueryID,
UserName,
SUM(TotalIOCount) TotalIOCount,
SUM(AmpCPUTime) TotalCPUTime,
SUM(SpoolUsage) TotalSpoolUsage
FROM
SYS_MGMT.DBQLogTbl_Hst
WHERE
logdate =:DT
AND UserName NOT IN ('arcuser1','arcuser2','arcuser3','arcuser4','arcuser5','arcuser6','arcuser7','arcuser8','DBC','DBCMANAGER','Crashdumps','Sys_Calendar','SysAdmin','Sysdba','SystemFe','tdwm')
GROUP BY 1,2,3,4
) a
) b
WHERE
CPUQryRank < 11;

LOCKING SYS_MGMT.DBQLogTbl_Hst FOR ACCESS
INSERT INTO SYS_MGMT.ResIntQry
(
QryDate,
ProcID,
QueryID,
UserName,
TotalIOCount,
TotalCPUTime,
TotalSpool,
QryRank,
Flg,
LogDate
)
SELECT
LogDate,
ProcID,
QueryID,
UserName,
TotalIOCount,
TotalCPUTime,
TotalSpoolUsage,
SpoolQryRank,
'S' Flg,
CURRENT_DATE
FROM
(
SELECT
LogDate,
ProcID,
QueryID,
UserName,
TotalIOCount,
TotalCPUTime,
TotalSpoolUsage,
ROW_NUMBER() OVER(PARTITION BY LogDate ORDER BY TotalSpoolUsage DESC) SpoolQryRank
FROM
(
SELECT
LogDate,
ProcID,
QueryID,
UserName,
SUM(TotalIOCount) TotalIOCount,
SUM(AMPCPUTime) TotalCPUTime,
SUM(SpoolUsage) TotalSpoolUsage
FROM
SYS_MGMT.DBQLogTbl_Hst
WHERE
logdate =:DT
AND UserName NOT IN ('arcuser1','arcuser2','arcuser3','arcuser4','arcuser5','arcuser6','arcuser7','arcuser8','DBC','DBCMANAGER','Crashdumps','Sys_Calendar','SysAdmin','Sysdba','SystemFe','tdwm')
GROUP BY 1,2,3,4
) a
) b
WHERE
SpoolQryRank < 11;
);
When I execute the following sql
SELECT
LogDate,
ProcID,
QueryID,
UserName,
TotalIOCount,
TotalCPUTime,
TotalSpoolUsage,
CPUQryRank,
'C' Flg,
CURRENT_DATE
FROM
(
SELECT
LogDate,
ProcID,
QueryID,
UserName,
TotalIOCount,
TotalCPUTime,
TotalSpoolUsage,
ROW_NUMBER() OVER(PARTITION BY LogDate ORDER BY TotalIOCount DESC) CPUQryRank
FROM
(
SELECT
LogDate,
ProcID,
QueryID,
UserName,
SUM(TotalIOCount) TotalIOCount,
SUM(AmpCPUTime) TotalCPUTime,
SUM(SpoolUsage) TotalSpoolUsage
FROM
SYS_MGMT.DBQLogTbl_Hst
WHERE
logdate =date-1
AND UserName NOT IN ('arcuser1','arcuser2','arcuser3','arcuser4','arcuser5','arcuser6','arcuser7','arcuser8','DBC','DBCMANAGER','Crashdumps','Sys_Calendar','SysAdmin','Sysdba','SystemFe','tdwm')
GROUP BY 1,2,3,4
) a
) b
WHERE
CPUQryRank < 11;

I get the following output
LogDate ProcID QueryID UserName TotalIOCount TotalCPUTime TotalSpoolUsage CPUQryRank Flg Date
1 5/16/2011 16,383 163,830,427,146,319,210 SAKKAMMADAM 284,341.00 68.10 6,469,504,000.00 1 C 5/17/2011
2 5/16/2011 16,383 163,830,427,146,300,900 SYS_MGMT 276,428.00 23.27 0.00 2 C 5/17/2011
3 5/16/2011 16,383 163,830,427,146,319,270 SAKKAMMADAM 261,688.00 71.02 2,137,085,952.00 3 C 5/17/2011
4 5/16/2011 16,383 163,830,427,146,319,271 SAKKAMMADAM 260,440.00 70.91 2,137,085,952.00 4 C 5/17/2011
5 5/16/2011 16,383 163,830,427,146,319,239 SAKKAMMADAM 258,500.00 70.52 1,979,600,896.00 5 C 5/17/2011
6 5/16/2011 16,383 163,830,427,146,319,219 SAKKAMMADAM 239,534.00 65.92 1,842,770,944.00 6 C 5/17/2011
7 5/16/2011 16,383 163,830,427,146,319,211 SAKKAMMADAM 166,393.00 44.12 1,280,629,760.00 7 C 5/17/2011
8 5/16/2011 16,383 163,830,427,146,306,181 SYS_MGMT 113,740.00 5.43 16,153,088.00 8 C 5/17/2011
9 5/16/2011 16,383 163,830,427,146,305,982 SYS_MGMT 113,562.00 5.47 16,153,088.00 9 C 5/17/2011
10 5/16/2011 16,383 163,830,427,146,306,182 SYS_MGMT 113,030.00 4.88 4,981,248.00 10 C 5/17/2011

But when I try to execute the macro
EXEC SYS_MGMT.InsResIntQry(DATE-1);
I get the following error:
EXECUTE Failed. 2616: Numeric overflow occurred during computation.

Why is this failing is a BIGINT represents a signed, binary integer value from -9,223,372,036,854,775,808 to
9,223,372,036,854,775,807. Surelye this accomodates my request? What am I missing?
2 REPLIES
Junior Contributor

Re: BIGINT QUESTION

None of the bigint columns causes that error, but you try to fit 163,830,427,146,319,210 into an integer :-)

QueryID should be DEC(18,0)

Dieter
Enthusiast

Re: BIGINT QUESTION

Wow that's it? AWESOME! I've been debugging and debugging and missed that simple thing!!!!! THANKS!!!