Space management issues

Former Member · ‎11-20-2014

Hello. I am completely new to Sybase IQ. I am trying to compare the performance of a stored procedure on SQL and Sybase IQ. I am using Sybase IQ version 16. I created a database and some tables and loaded the tables with data ( very large tables, records ranging from 10-40 million) . When I execute a stored procedure in this database , errors like

1) Could not execute statement.

main Bufman: All buffer cache pages are in use, ask your DBA to increase

the size of the buffer cache.

-- (slib\s_blockmap.cxx 12085)

SQLCODE=-1009031, ODBC 3 State="HY000"

Line 1, column 1

exec "DBA"."usp_create_det_f_create_so_to_del_to_inv"

and

2) IQ_System_temp is full.

How could I solve this issue and execute the stored procedure

Former Member · ‎11-20-2014

The stored procedure i use is as follows:

CREATE PROCEDURE "DBA"."usp_create_det_f_create_so_to_del_to_inv"

(

@debugenabled BIT = 0,

@clean BIT = 0

)

BEGIN

DECLARE @start_date DATETIME;

DECLARE @end_date DATETIME;

DECLARE @q1 DATETIME;

DECLARE @q2 DATETIME;

DECLARE @q3 DATETIME;

DECLARE @q4 DATETIME;

DECLARE @gjahr CHAR(4);

DECLARE @threshold FLOAT = 0.01;

DECLARE @procname VARCHAR(50);

SET @start_date = sf_getparam('GLOBAL','START_DATE');

SET @end_date = sf_getparam('GLOBAL','END_DATE');

SET @gjahr = sf_getparam('GLOBAL','GJAHR');

SET @procname = 'usp_create_det_f_create_SO_to_Del_to_INV';

SET @q1 = (SELECT max([date]) FROM _DET_F_PERIOD WHERE gjahr=@gjahr AND period_12=3) ;

SET @q2 = (SELECT max([date]) FROM _DET_F_PERIOD WHERE gjahr=@gjahr AND period_12=6);

SET @q3 = (SELECT max([date]) FROM _DET_F_PERIOD WHERE gjahr=@gjahr AND period_12=9);

SET @q4 = (SELECT max([date]) FROM _DET_F_PERIOD WHERE gjahr=@gjahr AND period_12=12);

BEGIN

CREATE TABLE _DET_F_CREATE_SO_TO_DEL_TO_INV (

id INT DEFAULT AUTOINCREMENT ,

fksto CHAR (1) NULL,

mandt varchar (3) NULL,

bukrs VARCHAR (4) NULL,

gjahr varchar (4) NULL,

belnr VARCHAR (10) NULL,

buzei VARCHAR (8) NULL,

hkont varchar (10) NULL,

shkzg CHAR (1) NULL,

fi_value_rc FLOAT NULL,

kunnr varchar (10) NULL,

kunnr_name VARCHAR (35) NULL,

budat date NULL,

fkdat date NULL,

fk_ernam VARCHAR (16) NULL,

fkart VARCHAR (4) NULL,

source_tab CHAR (4) NULL,

matnr VARCHAR (18) NULL,

maktx VARCHAR (40) NULL,

vbeln VARCHAR (10) NULL,

posnr VARCHAR (8) NULL,

vbtyp VARCHAR (1) NULL,

waers_lc VARCHAR (5) NULL,

waers_rc VARCHAR (5) NULL,

revenue_value_rc FLOAT NULL,

vgbel VARCHAR (10) NULL,

menge_meins FLOAT NULL,

sales_price_rc FLOAT NULL,

rev_sales_price_rc FLOAT NULL,

sd_referenced CHAR (1) NULL,

is_manual_posting CHAR (1) NULL,

is_service CHAR (1) NULL,

vbrk_waerk VARCHAR (5) NULL,

vbrp_netpr_lc FLOAT NULL,

lips_mandt VARCHAR (3) NULL,

likp_bukrs VARCHAR (4) NULL,

likp_gjahr CHAR (4) NULL,

likp_lfdat date NULL,

likp_vbtyp VARCHAR (1) NULL,

lips_vbeln VARCHAR (10) NULL,

lips_posnr VARCHAR (8) NULL,

lips_lgmng FLOAT NULL,

vbap_uebto FLOAT NULL,

vbap_uebtk VARCHAR (1) NULL,

lips_netpr_rc FLOAT NULL,

vbak_mandt VARCHAR (3) NULL,

vbak_vbtyp VARCHAR (1) NULL,

vbak_vbeln VARCHAR (10) NULL,

vbap_posnr VARCHAR (8) NULL,

vbap_netwr_rc FLOAT NULL,

vbap_netpr_rc FLOAT NULL,

vbap_netpr_lc FLOAT NULL,

vbak_waerk VARCHAR (5) NULL,

vbap_menge_calc_sku FLOAT NULL,

transactiontype VARCHAR (37) NULL,

quarter CHAR (2) NULL,

vbap_netpr_rc_enddate FLOAT NULL,

vbrp_netpr_rc_enddate FLOAT NULL,

quantity_so_by_invoiceline FLOAT NULL,

quantity_del_by_invoiceline FLOAT NULL,

quantity_inv_by_invoiceline FLOAT NULL,

price_inv_by_invoiceline FLOAT NULL,

price_so_inv FLOAT NULL,

quantity_so_inv FLOAT NULL,

quantity_so_del FLOAT NULL,

quantity_del_inv FLOAT NULL,

end_date DATETIME NULL,

vbak_audat date NULL,

vbrp_netpr_rc_perunit FLOAT NULL

) ;

INSERT INTO _DET_F_CREATE_SO_TO_DEL_TO_INV (

fksto

, mandt

, bukrs

, gjahr

, belnr

, buzei

, hkont

, shkzg

, fi_value_rc

, kunnr

, kunnr_name

, budat

, fkdat

, fk_ernam

, fkart

, source_tab

, matnr

, maktx

, vbeln

, posnr

, vbtyp

, waers_lc

, waers_rc

, revenue_value_rc

, vgbel

, menge_meins

, sales_price_rc

, rev_sales_price_rc

, sd_referenced

, is_manual_posting

, is_service

, vbrk_waerk

, vbrp_netpr_lc

, lips_mandt

, likp_bukrs

, likp_gjahr

, likp_lfdat

, likp_vbtyp

, lips_vbeln

, lips_posnr

, lips_lgmng

, vbap_uebto

, vbap_uebtk

, lips_netpr_rc

, vbak_mandt

, vbak_vbtyp

, vbak_vbeln

, vbap_posnr

, vbap_netwr_rc

, vbap_netpr_rc

, vbap_netpr_lc

, vbak_waerk

, vbap_menge_calc_sku

, transactiontype

, quarter

, vbap_netpr_rc_enddate

, vbrp_netpr_rc_enddate

, quantity_so_by_invoiceline

, quantity_del_by_invoiceline

, quantity_inv_by_invoiceline

, price_inv_by_invoiceline

, price_so_inv

, quantity_so_inv

, quantity_so_del

, quantity_del_inv

, end_date

, vbak_audat

, vbrp_netpr_rc_perunit

)

SELECT

r.fksto,

r.mandt,

r.bukrs,

r.gjahr,

r.belnr,

r.buzei,

r.hkont,

r.shkzg,

r.fi_value_rc,

r.kunnr,

r.kunnr_name,

r.budat,

r.fkdat,

r.fk_ernam,

r.fkart,

r.source_tab,

r.matnr,

r.maktx,

r.vbeln,

r.posnr,

r.vbtyp,

r.waers_lc,

r.waers_rc,

r.revenue_value_rc,

r.vgbel,

r.menge_meins,

r.sales_price_rc,

case when ROUND(r.menge_meins, 3) <>0 then r.revenue_value_rc/r.menge_meins end as rev_sales_price_rc,

r.sd_referenced,

r.is_manual_posting,

r.is_service,

ISNULL(dti.vbrk_waerk, oti.vbrk_waerk) as vbrk_waerk,

-- 0 as vbrp_netpr_lc,

case when dti.vbrp_netwr is not NULL then

(case when ROUND(dti.vbrp_fkimg, 3)<>0 AND v.umvkn<>0 AND v.fkimg<>0 AND v.umvkz<>0 then (dti.vbrp_netwr)/((v.fkimg*v.umvkz)/v.umvkn) else dti.vbrp_netwr end)

else

(case when ROUND(oti.vbrp_fkimg, 3)<>0 AND v.umvkn<>0 AND v.fkimg<>0 AND v.umvkz<>0 then oti.vbrp_netwr/((v.fkimg*v.umvkz)/v.umvkn) else oti.vbrp_netwr end)

end as vbrp_netpr_lc,

ISNULL(otd.lips_mandt, dti.lips_mandt) as lips_mandt ,

ISNULL(otd.likp_bukrs, dti.likp_bukrs) as likp_bukrs,

ISNULL(otd.likp_gjahr, dti.likp_gjahr) as likp_gjahr,

ISNULL(otd.likp_lfdat, dti.likp_lfdat) as likp_lfdat,

ISNULL(otd.likp_vbtyp, dti.likp_vbtyp) as likp_vbtyp,

ISNULL(otd.lips_vbeln, dti.lips_vbeln) as lips_vbeln,

ISNULL(otd.lips_posnr, dti.lips_posnr)as lips_posnr,

ISNULL(otd.lips_lgmng, dti.lips_lgmng) as lips_lgmng,

ISNULL(otd.vbap_uebto, oti.vbrk_uebto) as vbap_uebto,

ISNULL(otd.vbap_uebtk, oti.vbak_uebtk) as vbap_uebtk,

CASE WHEN otd.vbak_mandt IS NOT NULL THEN

(case when ROUND(otd.lips_lgmng, 3)>0 then otd.lips_netwr_rc/otd.lips_lgmng else 0 end)

ELSE

(case when ROUND(dti.lips_lgmng, 3)>0 then r.NETWR_RC/dti.lips_lgmng else 0 end)

END as lips_netpr_rc,

ISNULL(otd.vbak_mandt, oti.vbak_mandt) as vbak_mandt,

ISNULL(otd.vbak_vbtyp, oti.vbak_vbtyp) as vbak_vbtyp,

ISNULL(otd.vbak_vbeln, oti.vbap_vbeln) as vbak_vbeln,

ISNULL(otd.vbap_posnr, oti.vbap_posnr) as vbap_posnr,

ISNULL(otd.vbap_netwr_rc, oti.vbap_netwr_rc) as vbap_netwr_rc,

case when otd.vbap_netpr_rc IS NOT NULL then

(case when otd.vbap_kpein<>0 AND otd.vbap_umvkn<>0 AND otd.vbap_umvkz<>0

then case when otd.vbap_kpein<>otd.vbap_umvkz then otd.vbap_netpr_rc/((otd.vbap_kpein * otd.vbap_umvkz)/otd.vbap_umvkn) else otd.vbap_netpr_rc/(otd.vbap_umvkz/otd.vbap_umvkn) end

else otd.vbap_netpr_rc

end)

else

(case when oti.vbap_kpein<>0 AND oti.vbap_umvkn<>0 AND oti.vbap_umvkz<>0

then case when oti.vbap_kpein<>oti.vbap_umvkz then oti.vbap_netpr_rc/((oti.vbap_kpein * oti.vbap_umvkz)/oti.vbap_umvkn) else oti.vbap_netpr_rc/(oti.vbap_umvkz/oti.vbap_umvkn) end

else oti.vbap_netpr_rc

end)

END as vbap_netpr_rc,

case when otd.vbap_netpr_lc IS NOT NULL then

(case when otd.vbap_kpein<>0 AND otd.vbap_umvkn<>0 AND otd.vbap_umvkz<>0

then case when otd.vbap_kpein<>otd.vbap_umvkz then otd.vbap_netpr_lc/((otd.vbap_kpein * otd.vbap_umvkz)/otd.vbap_umvkn) else otd.vbap_netpr_lc/(otd.vbap_umvkz/otd.vbap_umvkn) end

else otd.vbap_netpr_lc

end)

else

(case when oti.vbap_kpein<>0 AND oti.vbap_umvkn<>0 AND oti.vbap_umvkz<>0

then case when oti.vbap_kpein<>oti.vbap_umvkz then oti.vbap_netpr_lc/((oti.vbap_kpein * oti.vbap_umvkz)/oti.vbap_umvkn) else oti.vbap_netpr_lc/(oti.vbap_umvkz/oti.vbap_umvkn) end

else oti.vbap_netpr_lc

end)

END as vbap_netpr_lc,

ISNULL(otd.vbak_waerk, oti.vbak_waerk) as vbak_waerk,

ISNULL(otd.vbap_menge_calc_sku, oti.vbap_menge_calc_sku) as vbap_menge_calc_sku,

case

when otd.vbak_mandt IS NOT NULL then 'Invoice with Delivery and Sales Order'

when oti.vbak_mandt IS NOT NULL then 'Invoice with direct Sales Order'

when oti.vbak_mandt IS NULL AND otd.vbak_mandt IS NULL then 'Invoice without referenced documents'

end as transactiontype,

d.[quarter] AS [quarter],

cast(0 AS FLOAT) AS vbap_netpr_rc_enddate,

cast(0 AS FLOAT) AS vbrp_netpr_rc_enddate,

cast(0 AS FLOAT) AS quantity_so_by_invoiceline,

cast(0 AS FLOAT) AS quantity_del_by_invoiceline,

cast(0 AS FLOAT) AS quantity_inv_by_invoiceline,

cast(0 AS FLOAT) AS price_inv_by_invoiceline,

cast(0 AS FLOAT) AS price_so_inv,

--null as Price_SO_DEL,

--null as Price_DEL_INV,

cast(0 AS FLOAT) AS quantity_so_inv,

cast(0 AS FLOAT) AS quantity_so_del,

cast(0 AS FLOAT) AS quantity_del_inv,

@end_date AS end_date,

ISNULL(otd.vbak_audat, oti.vbak_audat) as vbak_audat,

cast(0 AS FLOAT) AS vbrp_netpr_rc_perunit

FROM

_DET_F_CREATE_REVENUE_RECOGNITION r

LEFT JOIN VBRP v ON r.vbeln=v.vbeln AND r.posnr=v.posnr AND r.mandt=v.mandt

INNER JOIN _DET_F_PERIOD d ON r.budat=d.[date] AND r.bukrs=d.bukrs AND r.mandt=d.mandt

LEFT JOIN _AR_C_DELIVERY_TO_INVOICE_VG dti ON (r.vbeln=dti.vbrp_vbeln AND r.posnr=dti.vbrp_posnr AND r.mandt=dti.vbrp_mandt)

LEFT JOIN _AR_C_ORDER_TO_DELIVERY_VG otd ON (otd.likp_vbeln=dti.lips_vbeln AND otd.lips_posnr=dti.lips_posnr AND otd.likp_mandt IS NOT NULL AND otd.likp_mandt=dti.lips_mandt)

LEFT JOIN _AR_C_ORDER_TO_INVOICE_VG oti ON (r.vbeln=oti.vbrp_vbeln AND r.posnr=oti.vbrp_posnr AND r.mandt=oti.vbrp_mandt)

WHERE r.intimeframe = 'true';

CREATE INDEX _DET_F_CREATE_SO_TO_DEL_TO_INV_idx01 ON _DET_F_CREATE_SO_TO_DEL_TO_INV(vbak_vbeln);

CREATE INDEX _DET_F_CREATE_SO_TO_DEL_TO_INV_idx02 ON _DET_F_CREATE_SO_TO_DEL_TO_INV(vbap_posnr);

UPDATE sdi SET

sdi.lips_mandt = c.lips_mandt,

sdi.likp_bukrs = c.likp_bukrs,

sdi.likp_gjahr = c.likp_gjahr,

sdi.likp_lfdat = c.likp_lfdat,

sdi.likp_vbtyp = c.likp_vbtyp,

sdi.lips_vbeln = c.lips_vbeln,

sdi.lips_posnr = c.lips_posnr,

sdi.lips_lgmng = c.lips_lgmng,

sdi.lips_netpr_rc = (case when ROUND(c.lips_lgmng, 3)>0 then c.lips_netwr_rc/c.lips_lgmng else 0 end),

sdi.transactiontype = 'Invoice with Delivery and Sales Order'

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi

INNER JOIN _AR_C_ORDER_TO_DELIVERY_VG c ON (

c.lips_vgbel = sdi.vbak_vbeln

AND c.lips_vgpos = sdi.vbap_posnr

)

WHERE

sdi.vbak_vbeln IS NOT NULL

AND sdi.lips_mandt IS NULL ;

SELECT DISTINCT sdi.id

INTO

_TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_TO_REMOVE

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi

INNER JOIN VBAP v ON (v.vgbel=sdi.vbeln AND v.vgpos=sdi.posnr AND v.mandt = sdi.mandt )

INNER JOIN _AR_C_ORDER_TO_DELIVERY_VG otd ON (otd.vbak_vbeln=v.vbeln AND otd.vbap_posnr=v.posnr AND otd.vbak_mandt=v.mandt)

WHERE

sdi.transactiontype='Invoice without referenced documents'

AND otd.vbak_vbtyp <> 'H' ;

CREATE INDEX idx_tmp_det_f_create_so_to_del_to_inv_to_remove_belnr ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_TO_REMOVE (id);

INSERT INTO _DET_F_CREATE_SO_TO_DEL_TO_INV (

[fksto]

,[mandt]

,[bukrs]

,[gjahr]

,[belnr]

,[buzei]

,[hkont]

,[shkzg]

,[fi_value_rc]

,[kunnr]

,[kunnr_name]

,[budat]

,[fkdat]

,[fk_ernam]

,[fkart]

,[source_tab]

,[matnr]

,[maktx]

,[vbeln]

,[posnr]

,[vbtyp]

,[waers_lc]

,[waers_rc]

,[revenue_value_rc]

,[vgbel]

,[menge_meins]

,[sales_price_rc]

,[rev_sales_price_rc]

,[sd_referenced]

,[is_manual_posting]

,[is_service]

,[vbrk_waerk]

,[vbrp_netpr_lc]

,[lips_mandt]

,[likp_bukrs]

,[likp_gjahr]

,[likp_lfdat]

,[likp_vbtyp]

,[lips_vbeln]

,[lips_posnr]

,[lips_lgmng]

,[vbap_uebto]

,[vbap_uebtk]

,[lips_netpr_rc]

,[vbak_mandt]

,[vbak_vbtyp]

,[vbak_vbeln]

,[vbap_posnr]

,[vbap_netwr_rc]

,[vbap_netpr_rc]

,[vbap_netpr_lc]

,[vbak_waerk]

,[vbap_menge_calc_sku]

,[transactiontype]

,[quarter]

,[vbap_netpr_rc_enddate]

,[vbrp_netpr_rc_enddate]

,[quantity_so_by_invoiceline]

,[quantity_del_by_invoiceline]

,[quantity_inv_by_invoiceline]

,[price_inv_by_invoiceline]

,[price_so_inv]

,[quantity_so_inv]

,[quantity_so_del]

,[quantity_del_inv]

,[end_date]

,[vbak_audat]

,[vbrp_netpr_rc_perunit]

)

SELECT

sdi.fksto,

sdi.mandt,

sdi.bukrs,

sdi.gjahr,

sdi.belnr,

sdi.buzei,

sdi.hkont,

sdi.shkzg,

sdi.fi_value_rc,

sdi.kunnr,

sdi.kunnr_name,

sdi.budat,

sdi.fkdat,

sdi.fk_ernam,

sdi.fkart,

sdi.source_tab,

sdi.matnr,

sdi.maktx,

sdi.vbeln,

sdi.posnr,

sdi.vbtyp,

sdi.waers_lc,

sdi.waers_rc,

sdi.revenue_value_rc,

sdi.vgbel,

sdi.menge_meins,

sdi.sales_price_rc,

sdi.[rev_sales_price_rc],

sdi.sd_referenced,

sdi.is_manual_posting,

sdi.is_service,

sdi.vbrk_waerk,

sdi.vbrp_netpr_lc,

otd.lips_mandt as lips_mandt,

otd.likp_bukrs as likp_bukrs,

otd.likp_gjahr as likp_gjahr,

otd.likp_lfdat as likp_lfdat,

otd.likp_vbtyp as likp_vbtyp,

otd.lips_vbeln as lips_vbeln,

otd.lips_posnr as lips_posnr,

otd.lips_lgmng as lips_lgmng,

otd.vbap_uebto as vbap_uebto,

otd.vbap_uebtk as vbap_uebtk,

case when convert(FLOAT,otd.lips_lgmng)>0 then otd.lips_netwr_rc/otd.lips_lgmng else 0 end as lips_netpr_rc,

case when otd.vbak_mandt IS NOT NULL then otd.vbak_mandt end as vbak_mandt,

case when otd.vbak_vbtyp IS NOT NULL then otd.vbak_vbtyp end as vbak_vbtyp,

case when otd.vbak_vbeln IS NOT NULL then otd.vbak_vbeln end as vbak_vbeln,

case when otd.vbap_posnr IS NOT NULL then otd.vbap_posnr end as vbap_posnr,

case when otd.vbap_netwr_rc IS NOT NULL then otd.vbap_netwr_rc end as vbap_netwr_rc,

case when otd.vbap_netpr_rc IS NOT NULL then

(case when otd.vbap_kpein<>0 AND otd.vbap_umvkn<>0 AND otd.vbap_umvkz<>0

then case when otd.vbap_kpein<>otd.vbap_umvkz then otd.vbap_netpr_rc/((otd.vbap_kpein * otd.vbap_umvkz)/otd.vbap_umvkn) else otd.vbap_netpr_rc/(otd.vbap_umvkz/otd.vbap_umvkn) end

else otd.vbap_netpr_rc

end)

END as vbap_netpr_rc,

case when otd.vbap_netpr_lc IS NOT NULL then

(case when otd.vbap_kpein<>0 AND otd.vbap_umvkn<>0 AND otd.vbap_umvkz<>0

then case when otd.vbap_kpein<>otd.vbap_umvkz then otd.vbap_netpr_lc/((otd.vbap_kpein * otd.vbap_umvkz)/otd.vbap_umvkn) else otd.vbap_netpr_lc/(otd.vbap_umvkz/otd.vbap_umvkn) end

else otd.vbap_netpr_lc

end)

END as vbap_netpr_lc,

case when otd.vbak_waerk IS NOT NULL then otd.vbak_waerk

end as vbak_waerk,

case when otd.vbap_menge_calc_sku IS NOT NULL then otd.vbap_menge_calc_sku end as vbap_menge_calc_sku,

case

when otd.vbak_mandt IS NOT NULL then 'Invoice with Delivery and Sales Order'

end as transactiontype,

d.quarter as quarter,

cast(0 AS FLOAT) as vbap_netpr_rc_enddate,

cast(0 AS FLOAT) as vbrp_netpr_rc_enddate,

cast(0 AS FLOAT) as quantity_so_by_invoiceline,

cast(0 AS FLOAT) as quantity_del_by_invoiceline,

cast(0 AS FLOAT) as quantity_inv_by_invoiceline,

cast(0 AS FLOAT) as price_inv_by_invoiceline,

cast(0 AS FLOAT) as price_so_inv,

cast(0 AS FLOAT) as quantity_so_inv,

cast(0 AS FLOAT) as quantity_so_del,

cast(0 AS FLOAT) as quantity_del_inv ,

@end_date as end_date,

otd.vbak_audat as vbak_audat,

cast(0 AS FLOAT) as vbrp_netpr_rc_perunit

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi

INNER JOIN _DET_F_PERIOD d ON (

sdi.mandt=d.mandt AND sdi.bukrs=d.bukrs AND sdi.budat=d.[date]

)

INNER JOIN VBAP v ON v.mandt=sdi.mandt AND v.vgbel=sdi.vbeln AND v.vgpos=sdi.posnr

INNER

JOIN _AR_C_ORDER_TO_DELIVERY_VG otd ON otd.vbak_mandt=v.mandt AND otd.vbak_vbeln=v.vbeln AND otd.vbap_posnr=v.posnr

WHERE transactiontype='Invoice without referenced documents'

AND otd.vbak_vbtyp <> 'H' ;

DELETE

FROM _DET_F_CREATE_SO_TO_DEL_TO_INV

WHERE transactiontype='Invoice without referenced documents'

AND EXISTS (SELECT *

FROM _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_TO_REMOVE t

WHERE

_DET_F_CREATE_SO_TO_DEL_TO_INV.id = t.id

) ;

CREATE clustered INDEX cidx_det_f_create_so_to_del_to_inv ON _DET_F_CREATE_SO_TO_DEL_TO_INV(id);

CREATE INDEX idx_det_f_create_so_to_del_to_inv_lips_vbeln ON _DET_F_CREATE_SO_TO_DEL_TO_INV(lips_vbeln);

CREATE INDEX idx_det_f_create_so_to_del_to_inv_vbak_vbeln ON _DET_F_CREATE_SO_TO_DEL_TO_INV(vbak_vbeln);

UPDATE _DET_F_CREATE_SO_TO_DEL_TO_INV

SET transactiontype='Invoice without referenced documents'

WHERE transactiontype='Invoice with Delivery and Sales Order'

AND lips_vbeln IS NULL OR vbak_vbeln IS NULL ;

DROP INDEX _DET_F_CREATE_SO_TO_DEL_TO_INV.idx_det_f_create_so_to_del_to_inv_lips_vbeln;

DROP INDEX _DET_F_CREATE_SO_TO_DEL_TO_INV.idx_det_f_create_so_to_del_to_inv_vbak_vbeln;

SELECT

likp_mandt, likp_bukrs, lips_vgbel, lips_vgpos

,sum(lips_lgmng) as lips_lgmng_to_update

INTO

_TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_LIPS_LGMNG_TO_UPDATE

FROM

_AR_C_ORDER_TO_DELIVERY_VG

GROUP BY

likp_mandt, likp_bukrs, lips_vgbel, lips_vgpos ;

CREATE INDEX idx_tmp_det_f_create_so_to_del_to_inv_lips_lgmng_to_update ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_LIPS_LGMNG_TO_UPDATE(lips_vgbel, lips_vgpos);

CREATE INDEX idx_det_f_create_so_to_del_to_inv_lips_lgmng ON _DET_F_CREATE_SO_TO_DEL_TO_INV(lips_lgmng);

CREATE INDEX idx_det_f_create_so_to_del_to_inv_vbap_posnr ON _DET_F_CREATE_SO_TO_DEL_TO_INV(vbak_vbeln, vbap_posnr);

UPDATE sdi SET

sdi.lips_lgmng = upd_tbl.lips_lgmng_to_update

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi

INNER JOIN _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_LIPS_LGMNG_TO_UPDATE upd_tbl ON (

upd_tbl.lips_vgbel = sdi.vbak_vbeln

AND upd_tbl.lips_vgpos = sdi.vbap_posnr

AND upd_tbl.likp_mandt = sdi.vbak_mandt

)

WHERE

sdi.lips_lgmng = 0 ;

DROP INDEX _DET_F_CREATE_SO_TO_DEL_TO_INV.idx_det_f_create_so_to_del_to_inv_lips_lgmng;

DROP INDEX _DET_F_CREATE_SO_TO_DEL_TO_INV.idx_det_f_create_so_to_del_to_inv_vbap_posnr;

SELECT

s.mandt,

s.bukrs,

s.gjahr,

s.vbeln,

s.posnr ,

sum(distinct s.vbap_menge_calc_sku) as vbap_menge_calc_sku,

sum(s.lips_lgmng) as lips_lgmng,

case when count(*)<>0 then sum(s.menge_meins)/count(*) else sum(s.menge_meins) end as menge_meins,

case when count(*)<>0 then sum(s.sales_price_rc)/count(*) else sum(s.sales_price_rc) end as sales_price_rc

INTO

_TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_GRP_POSNR

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV s

GROUP BY

s.mandt, s.bukrs, s.gjahr, s.vbeln, s.posnr;

CREATE INDEX idx_tmp_det_f_create_so_to_del_to_inv_grp_posnr_i01 ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_GRP_POSNR (vbeln, posnr, bukrs, gjahr);

CREATE INDEX idx_det_f_create_so_to_del_to_inv_posnr ON _DET_F_CREATE_SO_TO_DEL_TO_INV (vbeln, posnr, bukrs, gjahr);

UPDATE

sdi

SET

sdi.quantity_so_by_invoiceline = upd_tbl.vbap_menge_calc_sku,

sdi.quantity_del_by_invoiceline = upd_tbl.lips_lgmng,

sdi.quantity_inv_by_invoiceline = upd_tbl.menge_meins,

sdi.price_inv_by_invoiceline = upd_tbl.sales_price_rc,

sdi.vbrp_netpr_rc_perunit = case when upd_tbl.lips_lgmng<>0

then vbrp_netpr_rc_enddate/upd_tbl.lips_lgmng

else 0

end FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi

INNER JOIN _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_GRP_POSNR upd_tbl ON (

upd_tbl.vbeln = sdi.vbeln

AND upd_tbl.posnr = sdi.posnr

AND upd_tbl.bukrs = sdi.bukrs

AND upd_tbl.gjahr = sdi.gjahr

AND upd_tbl.mandt = sdi.mandt

);

SELECT

s.mandt, s.bukrs, s.gjahr, s.belnr, s.buzei, s.vbeln, s.posnr, count(*) as split

INTO

_TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_POSNR

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV s

GROUP BY

mandt, bukrs, gjahr, belnr, buzei, vbeln, posnr

HAVING

count(*)>1 ;

CREATE INDEX idx__tmp_det_f_create_so_to_del_to_inv_posnr_posnr ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_POSNR (belnr, vbeln, posnr, bukrs, gjahr);

UPDATE sdi

SET sdi.revenue_value_rc = sdi.revenue_value_rc/split

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi

JOIN _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_POSNR a ON (

a.belnr = sdi.belnr

AND a.vbeln = sdi.vbeln

AND a.posnr = sdi.posnr

AND a.bukrs = sdi.bukrs

AND a.gjahr = sdi.gjahr

AND a.buzei = sdi.buzei

AND a.mandt = sdi.mandt

);

UPDATE sdi SET

sdi.price_so_inv =

case

when

(case when abs(quantity_so_by_invoiceline - quantity_inv_by_invoiceline) >= @threshold then abs(quantity_so_by_invoiceline - quantity_inv_by_invoiceline) else 0 end) = 0

AND sdi.vbap_netwr_rc = sdi.fi_value_rc then 0

else

ROUND(abs(sdi.vbap_netpr_rc_enddate - isnull(sdi.sales_price_rc,0)),2)

end

,quantity_so_inv = case when (quantity_so_by_invoiceline - quantity_inv_by_invoiceline) <= @threshold then abs(quantity_so_by_invoiceline - quantity_inv_by_invoiceline) else 0 end

,quantity_so_del = case when (quantity_so_by_invoiceline - quantity_del_by_invoiceline) <= @threshold then abs(quantity_so_by_invoiceline - quantity_del_by_invoiceline) else 0 end

,quantity_del_inv = case when abs(quantity_del_by_invoiceline - quantity_inv_by_invoiceline) >= @threshold then abs(quantity_del_by_invoiceline - quantity_inv_by_invoiceline) else 0 end

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi;

UPDATE sdi SET

sdi.price_so_inv =

case when

abs(((VBAP.netwr/VBAP.klmeng)/(VBAP.umvkz/VBAP.umvkn))-((VBRP.netwr/VBRP.fklmg)/(VBRP.umvkz/VBRP.umvkn))) <= @threshold

OR abs(((VBAP.netwr/VBAP.klmeng)/(VBAP.umvkz/VBAP.umvkn))-((VBRP.netwr/VBRP.fklmg))) <= @threshold

then 0 else

sdi.price_so_inv

end,

sdi.price_inv_by_invoiceline =

case when

abs(((VBAP.netwr/VBAP.klmeng)/(VBAP.umvkz/VBAP.umvkn))-((VBRP.netwr/VBRP.fklmg)/(VBRP.umvkz/VBRP.umvkn))) <= @threshold

then ((VBRP.netwr/VBRP.fklmg)/(VBRP.umvkz/VBRP.umvkn))

else

((VBRP.netwr/VBRP.fklmg))

end

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi

INNER JOIN VBAP ON sdi.vbak_vbeln=VBAP.vbeln AND sdi.vbap_posnr=VBAP.posnr

INNER JOIN VBRP ON sdi.vbeln=VBRP.vbeln AND sdi.posnr=VBRP.posnr

WHERE

price_so_inv <> 0

AND VBAP.klmeng <> 0

AND VBRP.fklmg <> 0

AND VBAP.umvkz <> 0

AND VBAP.umvkn <> 0

AND VBRP.umvkz <> 0

AND VBRP.umvkn <> 0;

SELECT

sdi.id, sdi.mandt

,sdi.vbak_vbeln, sdi.vbap_posnr

, cast(NULL AS VARCHAR(10)) as vbap_g_vbeln

, cast(NULL AS VARCHAR(8)) as vbap_g_posnr

,sdi.quantity_del_by_invoiceline

,sdi.quantity_inv_by_invoiceline

,sdi.quantity_so_by_invoiceline

, cast(NULL AS FLOAT) as so_quantity

, cast(0 AS BIT) as to_be_update

, cast(

case when

sdi.quantity_del_by_invoiceline < sdi.quantity_so_by_invoiceline

AND sdi.quantity_inv_by_invoiceline < sdi.quantity_so_by_invoiceline

then 1 else 0 end

AS BIT) as to_be_update_2

INTO

_TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi

WHERE

sdi.quantity_del_inv = 0

AND sdi.quantity_so_del = sdi.quantity_so_inv

AND sdi.quantity_so_del <> 0

--AND sdi.Quantity_SO_INV <> 0

AND sdi.transactiontype = 'Invoice with Delivery and Sales Order' ;

CREATE clustered INDEX cidx_tmp_det_f_create_so_to_del_to_inv_comp_quantity ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY (id) ;

CREATE INDEX idx_tmp_det_f_create_so_to_del_to_inv_comp_quantity_posnr ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY (vbak_vbeln, vbap_posnr);

UPDATE sdi SET

sdi.vbap_g_vbeln = vbap_g.vbeln

,sdi.vbap_g_posnr = vbap_g.posnr

,so_quantity = (vbap_g.netwr/vbap_g.netpr)

FROM

_TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY sdi

INNER JOIN VBAP AS vbap_c ON ( sdi.vbak_vbeln = vbap_c.vbeln AND sdi.vbap_posnr = vbap_c.posnr AND sdi.mandt = vbap_c.mandt

)

INNER JOIN VBAP AS vbap_g ON ( vbap_c.vgbel = vbap_g.vbeln AND vbap_c.vgpos = vbap_g.posnr AND vbap_c.mandt = vbap_g.mandt

)

WHERE

vbap_g.netpr <> 0 ;

CREATE INDEX idx_tmp_det_f_create_so_to_del_to_inv_comp_quantity_contract_posnr ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY (vbap_g_vbeln, vbap_g_posnr);

SELECT

sdi.mandt

,sdi.vbap_g_vbeln

,sdi.vbap_g_posnr

,sum(sdi.quantity_del_by_invoiceline) as quantity_del_by_invoiceline

,sum(sdi.quantity_inv_by_invoiceline) as quantity_inv_by_invoiceline

,max(sdi.so_quantity) as so_quantity

INTO

_TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_TO_VBAP

FROM

_TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY sdi

WHERE

sdi.vbap_g_vbeln IS NOT NULL

GROUP BY

sdi.mandt, sdi.vbap_g_vbeln, sdi.vbap_g_posnr

HAVING

max(sdi.so_quantity) >= sum(sdi.quantity_inv_by_invoiceline) ;

CREATE INDEX idx_tmp_det_f_create_so_to_del_to_inv_to_vbap_posnr ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_TO_VBAP (vbap_g_vbeln, vbap_g_posnr);

UPDATE sdi SET

sdi.to_be_update = 1

FROM

_TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY sdi

INNER JOIN _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_TO_VBAP c ON (

sdi.vbap_g_vbeln = c.vbap_g_vbeln

AND sdi.vbap_g_posnr = c.vbap_g_posnr

);

DROP INDEX _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY.idx_tmp_det_f_create_so_to_del_to_inv_comp_quantity_contract_posnr;

DROP INDEX _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY.idx_tmp_det_f_create_so_to_del_to_inv_comp_quantity_posnr;

CREATE INDEX idx_tmp_det_f_create_so_to_del_to_inv_comp_quantity_to_be_update ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY (to_be_update);

CREATE INDEX idx_tmp_det_f_create_so_to_del_to_inv_comp_quantity_to_be_update_2 ON _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY (to_be_update_2);

UPDATE sdi SET

sdi.quantity_so_del = 0

,sdi.quantity_so_inv = 0

FROM

_DET_F_CREATE_SO_TO_DEL_TO_INV sdi

INNER JOIN _TMP_DET_F_CREATE_SO_TO_DEL_TO_INV_COMP_QUANTITY cq ON (

sdi.id = cq.id

AND (cq.to_be_update = 1 OR cq.to_be_update_2 = 1)

);

UPDATE b

SET quantity_so_del = 0

, quantity_so_inv = 0

, quantity_del_inv = 0

FROM _DET_F_CREATE_SO_TO_DEL_TO_INV b

JOIN

(

SELECT vbak_vbeln

, vbap_posnr

, sum(case when revenue_value_rc<0 then quantity_inv_by_invoiceline*(-1) else quantity_inv_by_invoiceline end) as quantity_inv_by_invoiceline_by_soline

, max(quantity_del_by_invoiceline) AS quantity_del_by_invoiceline_by_soline

, max(quantity_so_by_invoiceline) AS quantity_so_by_invoiceline_by_soline

FROM _DET_F_CREATE_SO_TO_DEL_TO_INV sdi

WHERE transactiontype='Invoice with Delivery and Sales Order'

AND quantity_so_del = 0

AND (quantity_so_inv <> 0 OR quantity_del_inv <> 0)

GROUP BY vbak_vbeln, vbap_posnr

HAVING ROUND(ISNULL(SUM(CASE WHEN revenue_value_rc < 0

THEN quantity_inv_by_invoiceline*(-1)

ELSE quantity_inv_by_invoiceline END

), 0) - ISNULL(MAX(quantity_del_by_invoiceline), 0), 2) = 0

AND ROUND(ISNULL(MAX(quantity_del_by_invoiceline), 0) - ISNULL(MAX(quantity_so_by_invoiceline), 0), 2) = 0

AND ROUND(ISNULL(SUM(CASE WHEN revenue_value_rc < 0

THEN quantity_inv_by_invoiceline*(-1)

ELSE quantity_inv_by_invoiceline END

), 0) - ISNULL(MAX(quantity_so_by_invoiceline), 0), 2) = 0

) a ON a.vbak_vbeln=b.vbak_vbeln AND a.vbap_posnr=b.vbap_posnr

WHERE quantity_inv_by_invoiceline_by_soline = quantity_del_by_invoiceline_by_soline

AND quantity_inv_by_invoiceline_by_soline = quantity_so_by_invoiceline_by_soline

AND quantity_del_by_invoiceline_by_soline = quantity_so_by_invoiceline_by_soline ;

CREATE INDEX index_4 ON _DET_F_CREATE_SO_TO_DEL_TO_INV(kunnr ASC,mandt ASC, bukrs ASC, matnr ASC, budat ASC,gjahr ASC,belnr ASC,buzei ASC,fksto,fi_value_rc,

vbeln,posnr,vbtyp,revenue_value_rc,sd_referenced,is_service,likp_lfdat,lips_vbeln,lips_posnr,vbap_uebto,vbap_uebtk,

vbak_vbeln,vbap_posnr,transactiontype,vbap_netpr_rc_enddate,quantity_so_by_invoiceline,quantity_del_by_invoiceline,quantity_inv_by_invoiceline,

price_inv_by_invoiceline,price_so_inv,quantity_so_inv,quantity_so_del,quantity_del_inv,vbak_audat,vbrp_netpr_rc_perunit);

END

END;

Former Member · ‎11-20-2014

I increased the size of iq_system_temp to almost 4.5GB and increased the iqmc and iqtc parameters to 3gb. the stored procedure executed for almost like 1 hr 45 minutes and again the iq_system_temp got filled up. Do I have to make any changes in my stored procedure. This is a stored procedure i use in sql.. I made the necessary syntax changes and ran it in SYbase.

c_baker · ‎11-20-2014

Are you using only the IQ Demo database that comes with your installation, or have you created a new database and server?

You need to increase -iqtc in your .cfg file (and most likely -iqmc and -iqlm) to start with. IQ loves memory.

Also you will need to add space to IQ_SYSTEM_TEMP dbspace, and probably IQ_SYSTEM_MAIN. IQ does not dynamically grow disk storage.

There is a lot to learn about IQ and it should not be directly compared to SQL as it stores data differently than MS SQL and operates best as a Decision Support (DSS) analytics database, not as an OLTP database.

The documentation can be found at http://help.sap.com/iq

The SCN here also contains a lot of information that can help. You may also want to review the Hardware Sizing Guide for more detail on how IQ works and how to size it http://scn.sap.com/docs/DOC-41455

Chris

saroj_bagai · ‎11-20-2014

Most probably you have default configuration . Increase size of IQ_SYSTEM_TEMP(alter dbspace) and -iqmc and -iqtc in your configuration file.

SAP IQ 16.0 SP08 – SAP Help Portal Page

look at best practices guide and hardware sizeing guide

tayeb_hadjou · ‎11-20-2014

Hi Swetha,

For the first error, you need to increase main cache using startup parameters -iqmc in cfg file.

You may also need to increase temp cache, using parameter -iqtc.

The parameter values are in MB.

You have to restart IQ server after any cfg modification to be effective.

For the 2nd problem, extend accordingly your temporary dbspace: alter dbspace IQ_SYSTEM_TEMP add file <file_name> <full_path_file> size ...

While queries running, you can execute sp_iqdbspace in different sql DBA session to monitor usage percentage.

Regards,

Tayeb.

Space management issues

Accepted Solutions (0)

Answers (5)

Answers (5)

Re: How to show value help with values for Applica...

Re: Timer showing while sending the mails from SAP

Re: Timer showing while sending the mails from SAP

Re: Sac Dimension Comment

Re: Usage Procedure Logging SAP Tables