LOBにアクセスするSQLプロシージャを作成する際には常に注意が必要です。これは、次の理由によります。
- プロシージャはPEで実行されますが、LOBはAMPに保管されます。
- プロシージャは、AMP上に存在するLOBへの参照を保管します。
- LOBへのアクセスが必要なプロシージャのすべての操作では、プロシージャが実行されているPEにそのLOB全体を転送しなければなりません。
次の例は、XMLの販売データを含むCLOBを、販売テーブルに挿入される行のセットに変換します。プロシージャはさらに次の処理も行ないます。
- ログ テーブルにCLOBを保管します。
- ローカル変数にCLOBの半分を保管します。
- 別のログ テーブルにCLOBの半分を保管します。
CREATE TABLE sales ( partnum INTEGER, qtysold INTEGER, storecode INTEGER, salesdate DATE) PRIMARY INDEX (partnum, qtysold, storecode, salesdate); CREATE TABLE SalesLog ( storecode INTEGER, salesdate DATE, sales CLOB, logdate DATE, logtime TIME) PRIMARY INDEX (storecode, salesdate, logdate, logtime); CREATE TABLE saleshalflog ( storecode INTEGER, salesdate DATE, sales CLOB, logdate DATE, logtime TIME) PRIMARY INDEX (storecode, salesdate, logdate, logtime); CREATE PROCEDURE storessalesprocedure ( storecode INTEGER, salesdate DATE, salesclob CLOB) BEGIN DECLARE localclob CLOB; SET localclob = SUBSTR(:salesclob,1,CHARACTERS(:salesclob)/2 ); INSERT saleslog (:storecode, :salesdate, :salesclob, CURRENT_DATE, CURRENT_TIME ); INSERT saleshalflog (:storecode, :salesdate, :localclob, CURRENT_DATE, CURRENT_TIME ); INSERT sales SELECT * FROM TABLE (xmlparser(:salesclob)); END;
次のように、1 MBのCLOBを渡すことによってプロシージャが呼び出されるとします。
USING ( storecode INTEGER, salesdate DATE, salesclob CLOB AS DEFERRED) CALL storessalesprocedure (:storecode, :salesdate, :salesclob);
ここで次のプロセスが発生します。
- CLOBがクライアント アプリケーションからAMPに転送されます。
- プロシージャが1 MBのCLOBおよびその他のデータへの参照によって呼び出されます。
- プロシージャは、1 MBのCLOBのサブストリングを取ることによって、localclobと呼ばれる新しいCLOBを構築します。
- 1 MBのCLOBがすべてPEに転送されます。
- LocalClobで作成するために、0.5 MBのCLOBがAMPに転送されます。
- LocalClobへの参照がPEに保管されます。
- SalesLogテーブルへのINSERTが、1 MBのCLOBへの参照とともに構文解析プログラムに送信されます。
- 構文解析プログラムは、1 MBのCLOBへの参照を含むINSERTステップをAMPに送信します。
- AMPは、1 MBのCLOBが保管されているAMPでそれを検索し、SalesLogテーブルに挿入します。
- SalesHalfLogテーブルへのINSERTが、0.5 MBのCLOBへの参照とともに構文解析プログラムに送信されます。
- 構文解析プログラムは、0.5 MBのCLOBへの参照を含むINSERTステップをAMPに送信します。
- AMPは、0.5 MBのCLOBが保管されているAMPでそれを取得し、SalesHalfLogテーブルに挿入します。
- SalesテーブルへのINSERTが、1 MBのCLOBへの参照とともに構文解析プログラムに送信されます。
- 構文解析プログラムは、1MBのCLOBへの参照を含むINSERT ... SELECTステップをすべてのAMPに送信します。
- 1 MBのCLOBを含むAMPは、それを他のAMPにハッシュ再配布される行に変換します。
- それらの行がSalesテーブルに挿入されます。
LocalClobの使用は必須ではありません。ローカル変数を使用せずに、次のようにINSERT文にサブストリングの式を置くと、さらに効率的です。
INSERT INTO saleshalflog VALUES (:storecode, :salesdate, SUBSTR( :salesclob, 1, CHARACTERS(:salesclob)/2), CURRENT_DATE, CURRENT_TIME);
SQL文にLOB関数を置けない場合は、LOBデータ型をVARBYTEまたはVARCHARに変換することを検討してください。どちらを変換するにしてもLOB全体をPEに転送できますが、小さなLOBの場合、これはオプションです。
プロシージャ内部でのSELECT操作の場合、FOR構文を使用することによってLOBへの不要な転送を避けられます。次の例を考慮してみましょう。これは、LOBを選択し、それを別のテーブルに挿入するプロシージャの例です。
CREATE TABLE sales ( partnum INTEGER, qtysold INTEGER, storecode INTEGER, salesdate DATE) PRIMARY INDEX (partnum, qtysold, storecode, salesdate); CREATE TABLE saleslog ( storecode INTEGER, salesdate DATE, sales CLOB, logdate DATE, logtime TIME) PRIMARY INDEX (storecode, salesdate, logdate, logtime); CREATE PROCEDURE stores_sales_log_procedure (storecode INTEGER, salesdate DATE ) BEGIN FOR cur AS curname CURSOR FOR SELECT * FROM saleslog WHERE storecode = :storecode AND salesdate = :salesdate; DO IF (:cur.lobdate = DATE-1) THEN INSERT sales SELECT * FROM TABLE (xml_sales_parser(:cur.sales)); END IF; END FOR; END;
プロシージャstores_sales_log_procedureは、次のプロセスを実行します。
- :cur.salesに、販売CLOBへの参照を保管します。
- salesテーブル リクエストにINSERTリクエストを使用して、この参照を構文解析プログラムと、AMPに送られたINSERT ... SELECTステップに送信します。
- :cur.salesがローカル変数にコピーされると、CLOBはAMPからPEへ転送されます。
プロシージャのSELECT文のSELECT INTOスタイルおよびFETCH INTOスタイルはローカル変数に対してコピーを作成しますが、これによってAMPとPEの間でのLOBの余分な転送が発生します。
以下に、LOBデータの処理において効率的なプロシージャを作成ない例を示します。
CREATE TABLE sales ( partnum INTEGER, qtysold INTEGER, storecode INTEGER, salesdate DATE) PRIMARY INDEX (partnum, qtysold, storecode, salesdate); CREATE TABLE saleslog ( storecode INTEGER, salesdate DATE, sales CLOB, logdate DATE, logtime TIME) PRIMARY INDEX (storecode, salesdate, logdate, logtime); CREATE PROCEDURE stores_sales_stream_proc (storecode INTEGER, salesdate DATE, salesclob CLOB) BEGIN DECLARE resultrownum INTEGER; DECLARE partnum INTEGER; DECLARE qtysold INTEGER; INSERT INTO SalesLog VALUES (:storecode, :salesdate, :sales, CURRENT_DATE, CURRENT_TIME ); CALL xmlsalesparser(:resultrownum,:partnum,:qtysold,:salesclob); WHILE (resultrownum> 0 ) DO INSERT INTO Sales VALUES (:partnum, :qtysold, :storecode, :salesdate); CALL xmlsalesparser(:resultrownum,:partnum,:qtysold,:salesclob); END WHILE; END;