ITコーディネータのシュウです。

これは、昨年11月に島根の実家に帰省した際に、牡丹の花で有名な大根島の「由志園」でお土産に購入した牡丹の花を、父親が家の庭に植えていたものが、今年になって咲いたのを撮った写真です。綺麗に咲いたので、とても嬉しいですね。
現在、大根島は量、質ともに全国一の牡丹苗の生産地ということです。大根島は水田や農地が少なく養蚕と漁業以外に産業とよべるものはなかったそうですが、昭和３０年代から島の女性達は家族を養うために全国へ牡丹などの苗木を売り歩く行商に出かけたそうです。島の女性達によって美しい牡丹の花は全国にたくさんのファンを作り、大根島の名前は牡丹の花とともに知られるようになりました。機会があれば、また「由志園」に行ってみたいと思います。

＜本日の題材＞
一時テーブル使用時の照合順序（SQL Server）

照合順序については、以前もブログで取り上げたことがありますが、最近、お客様の開発案件に携わる中で、一時テーブルを使ったSQLで照合順序に関するエラーが発生し、対応した経緯がありますので、ブログで取り上げてみたいと思います。

例)
以前ブログで取り上げた「照合順序（SQL Server）の異なるデータベース間のジョイン」と現象は同じことになりますが、SQL Serverの照合順序と、あるシステム用に作成したデータベースの照合順序が異なるときに、一時テーブルを利用し、かつ、データベース上のテーブルと一時テーブルをジョインするようなときに起きる現象になります。
サーバーのプロパティで確認できる「サーバーの照合順序」は「Japanese_CI_AS」であるSQL Serverを使用するとします。

ここで、今回のシステムで使用する新しいデータベースの照合順序を、「Japanese_CS_AI_KS_WS」とします。（日本語で、大文字と小文字、ひらがなとカタカナ、全角と半角を区別する照合順序）

この環境で一時テーブルを作成した場合、一時テーブルは、システムデータベースの「tempdb」に作成されるため、一時テーブルの照合順序は「tempdb」の照合順序の設定「Japanese_CI_AS」に従って作成されます。

新しく作成したデータベースで、社員マスタと部門マスタを作成して、以下のようにJOINしてデータを抽出します。

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

これを、部門マスタを一時テーブルに変更して実行してみます。

CREATE TABLE #bumon(
BUMON_CD varchar(3) NOT NULL,
BUMON_NAME varchar(20) NULL);

INSERT INTO #bumon
SELECT * FROM dbo.BUMON;

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN #bumon b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

すると、下記のようなエラーが返ってきます。
「メッセージ 468、レベル 16、状態 9、行 13
equal to 操作の "Japanese_CI_AS" と "Japanese_CS_AI_KS_WS" 間での照合順序の競合を解決できません。」

テーブルの存在するデータベースとtempdbデータベースの照合順序が異なるためにこのエラーが起きることが確認されました。

そのため、前回のブログでも取り上げたように、ジョインする項目にCOLLATE句を使用するか、一時テーブルを作成するときに、COLLATE句を設定することで、エラーを回避できます。

ジョインの際にCOLLATE句を使用する場合：

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN #bumon b
ON s.BUMON_CD = b.BUMON_CD COLLATE Japanese_CS_AI_KS_WS
ORDER BY b.BUMON_CD, s.SYAIN_CD;

一時テーブルを作成するときに、COLLATE句を使用する場合：

CREATE TABLE #bumon(
BUMON_CD varchar(3) COLLATE Japanese_CS_AI_KS_WS NOT NULL,
BUMON_NAME varchar(20) COLLATE Japanese_CS_AI_KS_WS NULL);

一時テーブルとのジョインでもエラーにならずに抽出できることが確認されました。使用するデータベースの照合順序を、SQL Serverの照合順序と異なるものを使用するときには、注意が必要ですね。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

毎朝、自転車で通っている道にコスモスが植えてあるので、ちょこっと写真を撮ってみました。

さて、dbSheetClientのユーザー事例にＪＲ九州様が載りました。
Excelを利用して行ってきた勘定明細内訳システムを、dbSheetClientを採用してシステム化したということ。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/jr_kyushu.html

ＪＲ九州様と言えば、東京証券取引所に株式を上場したということで、とてもホットなニュースになっていますね。今年の大型IPO（新規株式公開）案件としては、LINEに次ぐ案件として注目を浴びていましたが、初値は公募価格を大きく上回る金額になったとか。今後の動きにも注目です！

＜本日の題材＞
一時テーブルのキー（SQL Server）

以前、一時テーブルについての記事をアップしたことがありました。一時テーブル、一時テーブル（２）。そのときには、一時テーブルの主キーやインデックスなどのキーについては特に記事としては上げていませんでしたが、今回、実際のシステムの開発時に気付いたことがありましたので、それについて取り上げてみたいと思います。

 ストアドプロシージャなどの中で、一時テーブルを作成してそこに一旦データを登録し、それを使っていろいろな条件によって処理を実行するようなことがよくあるかと思いますが、データ量が多くなると、やはり処理速度が遅くなってきます。
（一時テーブルは、SQL Serverの場合は、「tempdb」というシステムデータベースを利用して処理が行われていますし、Oracleの場合も、テンポラリー用の表領域を使うかたちになります。）

ですので、インデックスを作成することで、処理を早めるということは可能になります。このとき、主キーを設定したいと思う場合もあるかと思いますが、そのときには注意が必要です。
実は、SQL ServerをDBとして一時テーブルを使った処理のレスポンスをアップしようと思ったときに、主キーを設定したのですが、Create table文のときに、実テーブル作成時と同じように、Constraint句で主キー制約を設定し、制約名を設定しました。すると、そのプロシージャをほぼ同時のタイミングで複数のユーザで実行したところ、制約名が同じという理由で主キーを作成できないというエラーが起きてしまいました。
一時テーブルだから、別々のユーザで行う別々のセッションの処理では問題なく動作するはずという認識でいたのに、思わぬ落とし穴に引っかかってしまったような感覚でした。
ですので、一時テーブルでパフォーマンスを上げるためにインデックスが必要であれば、主キーではなく普通のインデックスを作成するほうが無難かと思われます。処理として主キー制約が必要な場合は、Create table 文で一時テーブルを作成した後に、Alter table文で主キーを追加するようにし、そのときに制約名を指定しないかたちを取る方法がよいかと思われます。

例）
以前ブログに一時テーブルの内容を取り上げたときの例を使い、主キーを設定して試してみたいと思います。そのときに、処理がすぐに終わらないように、一時テーブル作成後に、30秒間待機させるコマンド「WAITFOR DELAY」を設定します。（「スリープ処理」という標題で以前ブログで取り上げました）

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
   -- 一時テーブルの作成
       CREATE TABLE #顧客初回購入(
               顧客ID                 VARCHAR(8)  NOT NULL,
               初回購入日           DATETIME,
              CONSTRAINT PK_t顧客初回購入 PRIMARY KEY (顧客ID)
       );
       CREATE TABLE #顧客購入履歴(
               顧客ID              VARCHAR(8)  NOT NULL,
               累計購入回数    decimal(7),
               累計購入金額    decimal(9),
              CONSTRAINT PK_t顧客購入履歴 PRIMARY KEY (顧客ID)
       );

       WAITFOR DELAY '00:00:30'                -- 30秒間待機

   -- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #顧客初回購入
        SELECT 顧客ID, MIN(出荷日)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;

   -- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #顧客購入履歴
        SELECT 顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;

    -- 顧客購買履歴情報の表示
        SELECT
          A.顧客ID
        , FORMAT(A.初回購入日,'yyyyMMdd') AS 初回購入日
        , B.累計購入回数
        , B.累計購入金額
         FROM #顧客初回購入 A
         JOIN #顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

このプロシージャの実行を、２つのセッションで同時に行ってみます。
１つめのセッションで以下を実行：

DECLARE @顧客ID varchar(8)='K0000054'
EXEC dbo.顧客購買履歴情報抽出
 @顧客ID

すると、30秒後に以下のような結果が返ってきます。

上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。

DECLARE @顧客ID varchar(8)='K0000011'
EXEC dbo.顧客購買履歴情報抽出
 @顧客ID

すると、下記のようにエラーが返ってきます。

１つめのセッションのプロシージャで一時テーブルを作成し、それを使った処理が終わってないうちに、２つめのセッションの処理を行おうとすると、一時テーブル作成時に、主キーに設定した制約名が既に存在しているものと同じということでエラーになったということです。

ということなので、一時テーブルの作成時に主キーを設定する場合には、制約名を固定で指定しないかたちにしたほうがいいと思われます。
具体的には、以下のようにします。

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
   -- 一時テーブルの作成
       CREATE TABLE #顧客初回購入(
               顧客ID                 VARCHAR(8)  NOT NULL,
               初回購入日           DATETIME
       );
       CREATE TABLE #顧客購入履歴(
               顧客ID              VARCHAR(8)  NOT NULL,
               累計購入回数    decimal(7),
               累計購入金額    decimal(9)
       );

    ALTER TABLE #顧客初回購入 ADD PRIMARY KEY (顧客ID);
    ALTER TABLE #顧客購入履歴 ADD PRIMARY KEY (顧客ID);

       WAITFOR DELAY '00:00:30'                -- 30秒間待機

   -- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #顧客初回購入
        SELECT 顧客ID, MIN(出荷日)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;

   -- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #顧客購入履歴
        SELECT 顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;

    -- 顧客購買履歴情報の表示
        SELECT
          A.顧客ID
        , FORMAT(A.初回購入日,'yyyyMMdd') AS 初回購入日
        , B.累計購入回数
        , B.累計購入金額
         FROM #顧客初回購入 A
         JOIN #顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

上記のように修正したプロシージャをコンパイルし、その実行を、再度２つのセッションで同時に行ってみます。

１つめのセッションで実行：
すると、30秒後に以下のような結果が返ってきます。

上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。

今度は、こちらもエラーは出ず、30秒後には上記のような結果が返ってきました。

プロシージャの実行時に、SQL Server Management Studio のほうで一時テーブルを確認してみると、下記のように、２つずつ一時テーブルが作成されているのがわかります。

また、主キーである必要がなく、インデックスの作成でよければ、インデックスを追加するかたちで行うこともできます。
この場合は、ALTER TABLE … ADD PRIMARY KEY の部分を、CREATE INDEX xxx …に変更します。具体的には、

CREATE INDEX IX_t顧客初回購入 ON #顧客初回購入(顧客ID);
CREATE INDEX IX_t顧客購入履歴 ON #顧客購入履歴(顧客ID);

上記に変更したプロシージャをコンパイルして実行しても、先ほどと同様にエラーなく処理は実行されました。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

今回も、ITC関連のネタです。ITコーディネータ協会では、毎年この時期にITCカンファレンスというものを開催しており、今年が14回目です。今年は10月30日(金)～31日(土)で行われます。
今年の大会テーマは「IoT新時代におけるＩＴコーディネータのエボリューション～クラウド・Industrie4.0の可能性～」。ドイツが取り組んでいるIndustrie4.0（第4次産業革命）というものを取り上げつつ、日本版Industrie 4.0と言われる「つながる工場」の動きやITコーディネータ協会としての取り組みを紹介するということです。
HPは以下：
http://www.itc.or.jp/activity/seminar/itc_conf2015/index.html

近年、スマートフォンに代表されるようなコンピュータを内蔵した機器がインターネットに接続され、さらには様々なモノにセンサーなどを取り付け通信ができ、それらがつながる世界というような内容でIOT（Internet of Things）「モノのインターネット」という言葉をよく耳にするようになりました。スマートハウスやスマート家電などのスマート～という名前もよく使われますね。「つながる工場」、今、ものづくりの世界で大きな変革のときを迎えつつあるようです。それらを駆使することで、大きな飛躍のチャンスの可能性があるかも知れません。

＜本日の題材＞
一時テーブル(2)

前回、一時テーブルの例としてOracleの場合を取り上げましたので、今回はSQL Serverについて例を上げて確認してみたいと思います。

SQL Serverの場合のローカル一時テーブル作成の構文：

CREATE TABLE #表名(
  列名  データ型
, 列名  データ型
, …
);

SQL Serverの場合のグローバル一時テーブル作成の構文：

CREATE TABLE ##表名(
  列名  データ型
, 列名  データ型
, …
);

一時テーブルの使用例として、売上テーブルから顧客の初回購入日や購買回数などの購買履歴の情報を抽出して表示するような単純な処理を、ストアド・プロシージャで実行する簡単な例を作成してみます。

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
        -- 一時テーブルの作成
        CREATE TABLE #顧客初回購入(
                顧客ID                  VARCHAR(8),
                初回購入日              DATETIME
                );
 
        CREATE TABLE #顧客購入履歴(
                顧客ID                  VARCHAR(8),
                累計購入回数    decimal(7),
                累計購入金額    decimal(9)
                );
 
-- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #顧客初回購入
        SELECT 顧客ID, MIN(出荷日)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;
 
-- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #顧客購入履歴
        SELECT 顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;
 
-- 顧客購買履歴情報の表示
        SELECT
          A.顧客ID
        , FORMAT(A.初回購入日,'yyyyMMdd') AS 初回購入日
        , B.累計購入回数
        , B.累計購入金額
         FROM #顧客初回購入 A
         JOIN #顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

Oracleとは違って、プロシージャの中で一時テーブルを作成し、それらをジョインして抽出結果を表示するサンプルになります。

上記のプロシージャに顧客IDのパラメータを渡して実行してみます。

DECLARE @顧客ID varchar(8)='K0000011'
EXEC dbo.顧客購買履歴情報抽出
  @顧客ID

別の顧客IDをパラメータで渡して、再度実行結果を確認すると、

DECLARE @顧客ID varchar(8)=' K0000054'
EXEC dbo.顧客購買履歴情報抽出
  @顧客ID

さて、SQLServerの場合のグローバル一時テーブルについては、あまり使ってみたことがないですが、どういうときに使うかを調べてみたところ、排他処理で使うケースがあるようです。

悲観的排他制御を行いたいときに、アプリケーションでコントロールする場合には、最初に排他用のフラグを立てて、そのフラグが立っている場合は、他のユーザは更新などの処理が実施できないというようなことを行うことがあると思います。
ただ、このフラグの制御を実テーブルで行うと、予期せぬかたちで処理が強制的に終了してしまった場合に、フラグが立ったままになってしまい、排他の状態が続いてしまって更新ができなくなってしまうなどというケースが出てきてしまうため、何らかのかたちで解除する方法を検討しなければなりません。

このときに、このフラグの制御を実テーブルではなく、グローバル一時テーブルを作成するというかたちで行うと、強制的に終了した場合でも、グローバルテーブルが自動的に削除されることで排他状態ではないと容易に判断できるということになるということです。

簡単に試してみたいと思います。

★排他をかけたいとき

CREATE TABLE ##排他チェック(
        USERID          VARCHAR(20),
        処理時刻                DATETIME
        );

INSERT INTO ##排他チェック
 SELECT '00001', GETDATE();

同一のセッションで、このグローバル一時テーブルの中身を確認してみます。

SELECT * FROM ##排他チェック;

これを、全く別のセッションで異なるUSERIDのメンバーが抽出してみます。

SELECT * FROM ##排他チェック;

 ローカル一時テーブルとは異なり、他のセッションからもデータを確認できます。

ここで、元のグローバル一時テーブルを作成したセッションの接続を解除、もしくは実行していたウィンドウの右上の×の箇所をクリックして閉じてから、再度先ほど確認できた別のセッションからデータを確認してみます。

グローバル一時テーブルが接続の解除とともに自動的に削除されたために、データの抽出ができないことが確認できます。

非常に簡単なテストではありますが、このグローバル一時テーブルの存在チェックを行って、存在していれば排他中のため更新不可であり、存在しなければ排他中ではないため、処理を実行してよいという制御をある程度行うことができそうです。

例えば
IF OBJECT_ID(N'tempdb..##排他チェック', N'U') IS NOT NULL
  print '他のユーザが使用中のため、実行できません'
ELSE
  print '処理を実行します'

上記の処理を、先ほどと同様のタイミングで実行すると、最初に他のセッションでグローバル一時テーブルを作成したタイミングでは、

グローバル一時テーブルを作成したセッションの接続を解除した状態では

実際には、後から接続して処理を実行する側でも、処理の開始時点で同様に排他用のグローバル一時テーブルを作成するという処理を行うかたちになると思います。

今回は、SQL Serverについて、ローカル一時テーブルだけでなく、グローバル一時テーブルについても試してみました。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

ラグビーのワールドカップでは、日本が24年ぶりの勝利、それも世界ランク３位の南アフリカを倒すという歴史的大金星を上げて、世界中が驚いたということがありました。開催地イギリスの地元の新聞も、「UNBELIEVABLE」」という題で大きく日本の勝利を取り上げ、会場やその近辺では日本人というだけで、「素晴らしい試合だった。おめでとう！」という祝福を受けたということです。
本当にスポーツは素晴らしいですね。

さて、マイナンバー制度の施行もいよいよ間近に迫ってきました。会社などでもいろいろと教育を受けられた方も多いと思います。ITコーディネータ協会でも、ITコーディネータ協会が進めるマイナンバーへの取組みという内容でHPで取り上げています。
http://www.itc.or.jp/mynumber/
興味のある方は、是非ご確認ください。

＜本日の題材＞
一時テーブル

ストアドプロシージャなどで複雑な処理を行うような場合に、1つのSQLで一度に結果を導き出すことは難しいため、第一段階としての抽出の結果を一時的に登録する一時テーブルを用意し、その結果を元に次のステップとしての必要な処理を行い、最終的に目的のデータを登録するとか、結果を抽出するということがよくあると思います。

この一時テーブルですが、Oracleの場合とSQL Serverの場合で多少使い方が異なります。
Oracleの場合は、セッション（デフォルトではトランザクション）が終了するとセッションの中で作成したデータは消えますが、テーブル自体は永続的なものであり、セッションが切れてもテーブルは残ります。そのため、プロシージャなどとは別に、あらかじめテーブルを作成しておく必要があります。
※デフォルト(ON COMMIT DELETE ROWS)ではトランザクションがコミットされると一時表のデータは削除されますが、設定によって(ON COMMIT PRESERVE ROWS)は、トランザクションが終了後もデータは残り、セッション終了時にデータが切り捨てられます。

これに対して、SQL Serverの場合、プロシージャなどの中で一時テーブル自体も作成し、データを登録して使うかたちで、セッションが終了するとテーブルも自動的に削除されます。
ただし、SQL Serverでは、ローカル一時テーブル（テーブル名が#で始まる）とグローバル一時テーブル（テーブル名が##で始まる）があり、一時テーブルを作成したセッションのみから参照できるローカル一時テーブルと、別セッションからも参照でき、全ての参照がなくなったときに自動的に削除されるグローバル一時テーブルという違いがあります。

Oracleの場合の一時表作成の構文：

CREATE GLOBAL TEMPORARY TABLE 表名(
  列名  データ型
, 列名  データ型
, …
[ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS]
);

一時テーブルの作成例：
CREATE GLOBAL TEMPORARY TABLE temp_sample1(
 CD VARCHAR2(10) PRIMARY KEY
,NAME VARCHAR2(20)
,SURYO NUMBER(7)
);

以前、ブログで使用した商品マスタ、及び売上テーブルから、2014年11月に購入した商品と数量について、一時テーブルに登録します。

INSERT INTO temp_sample1
SELECT U.商品CD, S.商品名, SUM(U.商品数量)
  FROM 売上テーブル U
  JOIN 商品マスタ S ON U.商品CD = S.商品CD
 WHERE TO_CHAR(受注日,'YYYYMM') = '201411'
 GROUP BY U.商品CD, S.商品名;

そのままデータを確認します。

SELECT * FROM temp_sample1
 ORDER BY CD;

このとき、別のユーザが異なるセッションでこの一時テーブルのデータを確認すると、

SELECT * FROM temp_sample1
 ORDER BY CD;

一時テーブルのデータは、他のセッションからは参照できないことがわかります。

また、データを登録したほうのセッションで、COMMITを実行してからSELECTしてみます。

COMMIT;

SELECT * FROM temp_sample1
 ORDER BY CD;

COMITすると、一時テーブルのデータは削除されることがわかります。

それでは、トランザクション終了後もデータが残る設定（ON COMMIT PRESERVE ROWS）の設定で一時テーブルを作成してみます。

CREATE GLOBAL TEMPORARY TABLE temp_sample2(
 CD VARCHAR2(10) PRIMARY KEY
,NAME VARCHAR2(20)
,SURYO NUMBER(7))
ON COMMIT PRESERVE ROWS
;

同様にデータを登録し、COMMITします。

INSERT INTO temp_sample2
SELECT U.商品CD, S.商品名, SUM(U.商品数量)
  FROM 売上テーブル U
  JOIN 商品マスタ S ON U.商品CD = S.商品CD
 WHERE TO_CHAR(受注日,'YYYYMM') = '201411'
 GROUP BY U.商品CD, S.商品名;

COMMIT;

データを確認してみます。

SELECT * FROM temp_sample2
 ORDER BY CD;

今度は、COMMITしてもデータは消えず、SELECTで確認することができました。
以上、まずOracleについての一時テーブルの例について取り上げてみました。

今日は以上まで

にほんブログ村