ITC シュウちゃん のすべての投稿

メモリ最適化テーブル変数(SQL Server)

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

IMG_4486

この写真は、前回のブログで写真を載せた、大根島にある「由志園」に行ったときに、ついでに訪れた、島根半島の先のほうにある「美保神社」で撮ったものです。私も高校生の時に一度兄に連れて行ってもらったことがあったというかすかな記憶がありましたが、それ以来なので、ほとんど初めてという感じでした。この神社の祭神は、えびす様(事代主神)と大国主神の后の三穂津姫で、商売繁盛、海上安全、五穀豊穣、夫婦和合などの守護神として祀られています。実家が、毎年、正月には美保神社のお札を送ってもらうようにしているということを、道すがら初めて聞きました。父親も数十年ぶりに参ることができたと喜んでいたので、少し遠回りにはなりましたが、行って良かったと思います。

<本日の題材>
メモリ最適化テーブル変数(SQL Server

SQL Server2014から実装されたIn-Memory OLTP 機能に、メモリ最適化テーブルというものがあります。今まであまり試したことがなかったのですが、今回は、そのメモリ最適化テーブル変数というものを試してみたいと思います。

例)
テスト用テーブルを作成し、テストデータを50万件作成します。そこから検索したいデータを抽出し、テスト用テーブルの列定義と同じ設定のメモリ最適化テーブル変数に登録して、結果を抽出してみます。

まず、メモリ最適化機能を使用するために、データベースに MEMORY_OPTIMIZED_DATA で宣言された FILEGROUPを作る必要があります。今回は、既存のデータベース「BLOG」に追加します。

ALTER DATABASE BLOG ADD FILEGROUP BLOG_mod CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE BLOG ADD FILE (name='BLOG_mod1', filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\iBLOG_mod1') TO FILEGROUP BLOG_mod;

blog102_2

次に、データを登録する「 mem_test」テーブルを作成します。

CREATE TABLE mem_test(
id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,Constraint PK_mem_teste Primary key(id));

blog102_3

このテーブルにテストデータを作成します。今回は、50万件を以下のように作成します。「id」項目には、1~500000までシーケンシャルに値を設定し、名前は「顧客名_」の後にidを頭0埋めで設定、区分は2桁で、10,20,30, ~90までをランダムに、ポイントは、1~100の整数をランダムに登録してみます。ランダムに値を設定するのは、RAND関数と、NewID関数を使用してみます。

--データの作成
DECLARE @i int = 0
WHILE @i < 500000
BEGIN
   SET @i = @i + 1
   INSERT INTO mem_test(id, 名前, 区分, ポイント)
   VALUES(@i, '顧客名_'+ RIGHT('000000'+CAST(@i as nvarchar),6), CAST(FLOOR(1 + RAND()*9)*10 AS NVARCHAR), 1+ABS(CHECKSUM(NewId())) % 100)
END

blog102_4

ランダム値についてですが、RAND関数は、0~1の間の数が生成されるので、指定範囲内の整数値乱数が必要な場合は、
SELECT FLOOR([FROM値] + (RAND() * ([TO値] - [FROM値] + 1)))
のようにすれば取得できます。今回は、10,20,..,90 という文字型の値にしたかったので、CAST(FLOOR(1 + RAND()*9)*10 AS NVARCHAR) としました。
また、NEWID関数は、uniqueidentifier データ型の値を返してきますが、データを数値にしてチェックするCHECKSUM関数と、負の数が返ってくる場合もあるため絶対値を取得するABS関数を使い、さらに求める値の範囲を考慮して、今回は1~100までの値のため、100で割った余りを使うかたちで、1+ABS(CHECKSUM(NewId())) % 100 としました。

データを確認してみます。
SELECT * FROM mem_test
ORDER BY id;

blog102_5

区分とポイントはランダムに値が登録されているのが確認できました。
それでは、メモリ最適化テーブル変数を使用するために、そのためのユーザー定義テーブル型を宣言します。通常と違うのは、「MEMORY_OPTIMIZED = ON」を付けることと、ハッシュインデックスを付与する点が異なります。

CREATE TYPE mem_test_type AS TABLE
(id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,INDEX idx1 NONCLUSTERED HASH (id) WITH ( BUCKET_COUNT = 500000 )
) WITH ( MEMORY_OPTIMIZED = ON );

blog102_6

※HASHインデックスでは、BUCKET_COUNT(バケット数)を適切な値へ設定していないと、性能低下の原因に繋がるようです。(特に小さすぎると性能が大きくさがるとのこと)

ちなみに、最初に行った、データベースに MEMORY_OPTIMIZED_DATA で宣言された FILEGROUPを作成していなかった場合には、上記のタイプを作成しようとしたときに、「メモリ最適化テーブル を作成できません。メモリ最適化テーブル を作成するには、オンライン状態かつ 1 つ以上のコンテナーがある MEMORY_OPTIMIZED_FILEGROUP がデータベースに含まれている必要があります。」というエラーが出ます。

blog102_8

それでは、上記を使ってメモリ最適化テーブル変数を宣言し、指定した5つの区分についてのデータ件数を確認してみたいと思います。

-- メモリ最適化テーブル変数の宣言
DECLARE @retValue dbo.mem_test_type

-- メモリ最適化テーブルへデータの INSERT
DECLARE
       @区分1 nvarchar(2) = '20',
       @区分2 nvarchar(2) = '40',
       @区分3 nvarchar(2) = '50',
       @区分4 nvarchar(2) = '70',
       @区分5 nvarchar(2) = '90';
 
   INSERT INTO @retValue
   SELECT id, 名前, 区分, ポイント
   FROM dbo.mem_test
   WHERE 区分 = @区分1
              OR 区分 = @区分2
              OR 区分 = @区分3
              OR 区分 = @区分4
              OR 区分 = @区分5
 
-- メモリ最適化テーブル変数に格納した結果を集計
SELECT 区分, COUNT(*) 件数
FROM @retValue
GROUP BY 区分
ORDER BY 区分;

blog102_7

ほとんど時間はかからずに結果が抽出されてきました。
メモリ最適化テーブル変数を、一時テーブルのようなかたちで使用できることが確認できましたが、実際には、もっと適した使用法があるのではないかと思います。今回は、取りあえず使えることを確認してみました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

再帰SQLでの最大再帰数(SQL Server)

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

IMG_4445

IMG_4451

大変ご無沙汰しています。久しぶりにブログをアップいたします。この写真は、島根の実家に帰省したときに、父親が行ってみたいと言った、宍道湖の隣にある中海に浮かぶ大根島にある「由志園」(ゆうしえん)で撮ったものです。私も大根島に行くこと自体が初めてだったので、由志園というものを知らなかったのですが、牡丹の栽培と雲州人参と呼ばれる高麗人参で有名な大根島にある、池泉回遊式(池をめぐり回る形式)日本庭園です。大根島は日本一の牡丹苗の生産地であり、ゴールデンウィークの時期は、園内の池に三万輪の牡丹が浮かべ、歩道の両脇を牡丹で絨毯のように敷き詰めるなど、とても綺麗だということです。また、日本の高麗人参の産地は、雲州(島根)、信州(長野)、会津(福島)の三地域で、大根島については、かつて高麗人蔘が門外不出の産物であったため、島で栽培をしていることを隠すために「人蔘島」ではなく、「大根島」と呼ぶようになったという逸話があるようです。
父親が行きたいと言ったので訪ねてみましたが、自分が生まれた田舎でも、学生の時から東京に出てきたため、行ったこともなく知らないところがたくさんあることを改めて感じました。

コロナウィルス感染が少し落ち着いてきているこの頃、また第6波がいつ来るかと危惧されていますが、国内産の経口薬などが早く開発されて、以前のように気兼ねなく自然や観光地を訪ねることができるようになればと願います。

<本日の題材>
再帰SQLでの最大再帰数(SQL Server

以前、このブログで取り上げた、WITH句と共通テーブル式(CTE)という内容の中で、再帰SQLの例を上げましたが、今回は、再帰SQLの最大再帰数の設定ができることについて取り上げたいと思います。

 例)
2021年の1年間の日付と曜日の一覧(カレンダー)を、WITH句を使って表示するSQLを作成したいと思います。その際に、1年分の日数の再帰を行って抽出すると、以下のようになります。

DECLARE @StartDate date = '20210101';             -- 手抜き (;^_^A
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 1, @StartDate));

;WITH seq(n) AS
(
SELECT 0
UNION ALL
SELECT n + 1
FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d, youbi) AS
(
SELECT DATEADD(DAY, n, @StartDate),
DATENAME(WEEKDAY, DATEADD(DAY, n, @StartDate))
FROM seq
)
SELECT d, youbi
FROM d
ORDER BY d;

最初のWITH句で、@ StartDate(2021/1/1)から、@CutoffDate date(@StartDateから1年後-1日、つまり2021年の年末)までの日数分の 0~364までの365件のレコードを再帰処理を使って作成しています。(DECLARE @StartDate date = '20210101'; は、本来は'2021-01-01'ですが、暗黙の型変換で日付と認識してくれています。)
その後、次のWITH句の内容で、それを使ってその期間の日付と曜日のレコードを作成し、最後にそれを抽出する処理になります。ちなみに、何曜日を表わすのに、DATENAME関数を使っています。
しかし、これを実行すると、最大再帰数に達成したというエラーが表示されます。

blog101_3

「ステートメントが終了しました。ステートメントの完了前に最大再帰数 100 に達しました。」というエラーメッセージが表示されます。再帰SQLでは、無限ループ等が発生した場合のことを考慮して、再起の出来る回数の上限値が設定されており、100行を超えるとエラーになるようです。
この上限値を変更するのが、「MAXRECURSION」というオプションになります。

設定方法は
OPTION (MAXRECURSION 上限値)
で、設定できる上限値は、0 ~ 32767 で、「0」は上限なし、デフォルトは「100」です。

先ほどのSQLに、OPTION (MAXRECURSION 0) を追加して実行してみます。

blog101_4

実行すると、エラーなく 365件分、2021/1/1~2021/12/31までの日付と曜日が表示されます。今回は、上限なしの「0」を設定しましたが、1年の日数である「365」を設定しても、同様の結果になります。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

SEQUENCE(シーケンス)の現在値の変更(SQL Server)

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

IMG_3308

お久しぶりです。この写真は2か月前くらいに、島根の実家に帰省する際に乗った、寝台特急「サンライズ出雲」の、ノビノビ座席に乗ったときに撮ったものです。寝台車というと、個室を思い浮かべますが、最近は頭の部分だけが仕切られていて、そんなにそれぞれのスペースは広くはないですが、カーペットの座敷で横になってしっかり寝れるようなタイプの格安の席があります。
ちょうど、高速バスがコロナ禍で運行していない時期で、帰省する手段を探したところ見つかり、寝台車に久しぶりに乗りましたが、横になって休める分、バスよりもゆっくり眠れるような気がします。

最近再びコロナの感染者が増えてきており、様々心配は尽きませんが、こういうときこそ絆を大切にし、励ましあいながら、頑張って乗り越えて行きたいものです。
コロナウィルスを始め、様々なことがあったこの1年もいよいよ終わろうとしています。皆様も健康に気を付けてよいお年をお迎えください。

<本日の題材>
SEQUENCE(シーケンス)の現在値の変更(SQL Server

以前、SEQUENCEについてブログで取り上げましたが、SEQUENCEの現在の値を修正したいというケースが発生する場合があります。例えば、主キーの値をシーケンスを使って採番するようにしていたが、データのメンテナンスが必要になり、データをコピーして追加したところ、主キーの値がシーケンスの値と大きくかけ離れた大きな値になってしまったというようなケースです。私も実際に複数のテーブルでそういうケースにぶつかってしまい、一つずつ、シーケンスの設定を変更しなければいけないという状況が発生しました。このとき、テーブルの主キーの値の最大値をSQLで確認した後、SQL ServerManagement Studioで1つ1つのシーケンスの現在値を変更していきましたが、もっとスムーズに設定の変更ができる方法がないかを探したときに、対応した方法です。

例)
「testA」テーブルの主キー「id」(int型)をSEQUENCE「test_seqA」を使って採番するかたちにします。
100番までSEQUENCEを使って採番した後、データのメンテナンスが発生して、120番までデータを追加しましたが、そのときにはSEQUENCEを使って採番しなかったので、現在値が100のままになっているので、そのままでは次にSEQUENCEを使ってデータを登録すると、主キーの重複でエラーになってしまいます。ですので、SEQUENCEの現在の値を120に修正する必要があります。

まず、SEQUENCE「test_seqA」の現在の値を確認してみます。

SELECT current_value FROM sys.sequences WHERE name = 'test_seqA';

blog100_1

100という結果が出ました。

次に、テーブル「testA」の主キー「id」の最大値を確認してみます。

SELECT MAX(id) FROM testA;

blog100_2

120という値が確認されました。

SEQUENCEの現在の値を120に修正する方法の1つは、ALTER SEQUENCE文で修正を行う方法です。

ALTER SEQUENCE test_seqA
RESTART WITH 120
INCREMENT BY 1;

(ちなみに、SQL Server Management Studioでシーケンスのプロパティで、「シーケンスの再開」から変更することも可能です)

上記の作業を順に行えば修正はできますが、毎回、いちいち上記の処理を順番に行うのも面倒なので、今回は、上記のALTER SEQUENCE文を使用するのではなく、下記のようなSQL文を作成して、それを実行するかたちにします。

DECLARE
@id_MAX   INT
,@Seq_cur_val   sql_variant
,@count         INT = 0;

SELECT @id_MAX = MAX(id)
FROM testA;

SELECT @Seq_cur_val = CURRENT_VALUE FROM sys.sequences
WHERE name = ‘test_seqA’;

IF @id_MAX > CAST(@Seq_cur_val AS INT)
BEGIN
WHILE @count < @id_MAX
BEGIN
              SELECT @count = NEXT VALUE FOR test_seqA;
END;
END;

実行します。

blog100_3

実行後、SEQUENCE「test_seqA」の現在の値を確認してみます。

SELECT current_value FROM sys.sequences WHERE name = 'test_seqA';

blog100_4 

現在値が120に変更されているのが確認できます。
この状態であれば、「tetA」テーブルにSEQUENCE「test_seqA」を使って主キーを採番することが可能です。次の採番データを登録して見ます。

INSERT INTO testA(id) VALUES(NEXT VALUE FOR test_seqA);

blog100_5

データは登録されました。データの主キーの値が大きいものから10件確認してみます。

SELECT TOP 10 * FROM testA
ORDER BY id DESC;

blog100_6

正しく採番されていることが確認できました。
ちなみに、dbSheetClientでは、上記のSQLのテーブル名、主キー名、SEQUENCE名などを、Excelのシートから値を渡すことができますので、上記のSQLを実行するタスクを1つ作成して、それを各テーブルで必要なタイミングで呼び出すようにすることで、複数のテーブルにおいてもSEQUENCEの現在値を修正することが可能です。これを使うことで、何とか問題なく処理をすることが可能になり、とても助かりました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

CASE式をWHERE句で使う(SQL Server)

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

IMG_3316

お久しぶりです。写真は妻が買ってきて家に置いていたハイビスカスという花が綺麗に咲いたところを撮ったものです。ハイビスカスは花の色によって花言葉が違うようですね。白は「艶美」、ピンクは「華やか」、黄色は「輝き」、赤は「勇敢」とのこと。
家に花があるのはいいものですね。あまり花とかに興味がなかった私でしたが、妻のおかげで自然の美しさを意識することが多くなったと感謝しています。
一生懸命に咲いて美を表わそうとする花のように、私たちも情熱をもって一生懸命に生きていきたいものです。

それから、dbSheetClientのブログに、はるパパさんのdbSheetClient千夜一夜というブログができています。是非そちらもご覧ください。
https://www.newcom07.jp/dbsheetclient/dbsc-blog/

<本日の題材>
CASE式をWHERE句で使う(SQL Server

CASE WHENの式を、以前、SELECT文で使用するサンプルは何度かブログで取り上げましたが、WHERE句の中で、ケースによって条件文自体を変更したいという場合のやり方を今回試してみたいと思います。

例)
検索用の項目を設定し、そこに値を設定すれば、その項目に対しては指定した値で検索するが、何も設定しなければ、その項目に対しては条件を設定せずに抽出を行いたいというケースはよくあると思います。

今回は簡単なケースとして、社員マスタの検索の際に、「社員番号」「社員名」「拠点」の検索項目に対して、設定した内容があればその値で検索し、なければ条件としては加えないかたちの例を取り上げてみたいと思います。

上記の3項目をパラメータとして渡し、値が NULL でなければその値を条件とするが、NULLの場合には条件に加えないというかたちにします。

例えば、社員番号、社員名は何も値を設定せず、拠点を「ニューヨーク」として検索したい場合、以下のように設定できます。

-- 変数を定義
DECLARE
            @社員番号     DECIMAL(5),
            @社員名       NVARCHAR(20),
            @拠点         NVARCHAR(20);

--変数設定:画面で選択した値がセットされるようにします
SET @社員番号 = NULL;
SET @社員名 = NULL;
SET @拠点 = 'ニューヨーク' ;

--社員マスタ検索
SELECT * FROM dbo.社員マスタ
WHERE 1=1
AND 社員番号 = CASE WHEN @社員番号 IS NULL THEN 社員番号 ELSE @社員番号 END
AND 社員名 = CASE WHEN @社員名 IS NULL THEN 社員名 ELSE @社員名 END
AND 拠点 = CASE WHEN @拠点 IS NULL THEN 拠点 ELSE @拠点 END;

SQL1

上記は、拠点が「ニューヨーク」の社員を全て抽出してきます。
もちろん、社員名にも値を設定すれば2つの条件で抽出することになります。

SQL2

 結果は、2つの条件を満たすデータのみが抽出されることを確認できました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

データベーストリガー(SQL Server)

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

IMG_2842

お久しぶりです。写真は4月の初め頃に撮った桜の写真です。早くブログにアップしようと思いながら、4月から新しく関わったプロジェクトのほうも忙しく、1か月があっという間に過ぎてしまいました。
さて、今年の初めには予想もしていなかったコロナウィルスの影響で、世界が全く違うものになったような現状に、本当に驚き戸惑うことが多い毎日です。ウィルスとの闘いの最先端で苦労されている医療従事者の方には本当に感謝をしなければならないと思います。ゴールデンウイークもほぼ自宅にいるという状況で、映画のDVDを借りてきて家族で見るとか、そんな過ごし方をせざるを得ない状況ではありますが、国民が一つとなってこのときを乗り越えていけるよう、そして1日も早く終息することができるように心からお祈りいたします。

<本日の題材>
データベーストリガー(SQL Server)

データベーストリガーについて、以前、Oracleで取り上げましたが、今回はSQL Serverにて同様の設定について確認してみたいと思います。
SQL Serverトリガーには、大きくは「ログオントリガー」「DDLトリガー」「DMLトリガー」の3つの種類があり、それぞれは以下のような内容になります。
「ログオントリガー」は、ログオン時ユーザーセッションが確立されるときに実行するもの
「DDLトリガー」は、DDL操作(CREATE, ALTER, DROPなど)により起動するもの
「DMLトリガー」は、特定の表やビューに対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動するもの

前回、Oracleで「DMLトリガー」の「AFTERトリガー」を使った例を紹介しましたが、今回はSQLServerで同様の設定をしてみたいと思います。

例)
前回同様、ユーザー管理用の「ACCOUNTS」テーブルというものがあったとして、それに対しての、INSERT, UPDATE, DELETE が行われるタイミング(AFTER)で、処理のログを「ACCOUNTS_LOG」テーブルに登録するトリガーを作成してみます。
まず、処理のログを登録する「ACCOUNTS_LOG」テーブルを作成します。

CREATE TABLE accounts_log(
log_date DATE,
old_account_id INT,
new_account_id INT,
old_name NVARCHAR(20),
new_name NVARCHAR(20),
action NVARCHAR(20));

次に、トリガーを作成しますが、「ACCOUNTS」テーブルに対しての、INSERT, UPDATE, DELETE が行われてデータが更新されるたびに、「ACCOUNTS_LOG」テーブルに、処理日、変更前後の「ACCOUNT_ID」、「NAME」そして処理内容を記録させます。

--INSERT、DELETEの履歴登録用
CREATE TRIGGER accounts_ins_del_trigger
ON accounts
AFTER INSERT, DELETE
AS
BEGIN
INSERT INTO accounts_log (log_date, old_account_id, new_account_id, old_name, new_name, action)
SELECT GETDATE(), NULL, account_id, NULL, name, 'INSERT'
FROM inserted
UNION
SELECT GETDATE(), account_id, NULL, name, NULL, 'DELETE'
FROM deleted;
END;
GO

--UPDATEの履歴登録用
CREATE TRIGGER accounts_upd_trigger
ON accounts
AFTER UPDATE
AS
BEGIN
INSERT INTO accounts_log (log_date, old_account_id, new_account_id, old_name, new_name, action)
SELECT GETDATE(), account_id, NULL, name, NULL, 'UPDATE'
FROM deleted
UNION
SELECT GETDATE(), NULL, account_id, NULL, name, 'UPDATE'
FROM inserted
END;
GO

前回同様下記のように、「ACCOUNTS」テーブルへのデータの登録、修正、削除を順に行ってみます。

INSERT INTO accounts(account_id, name) VALUES(101, '山田 太郎');
INSERT INTO accounts(account_id, name) VALUES(102, '高橋 三郎');
UPDATE accounts SET NAME = '高橋 一郎' WHERE account_id = 102;
DELETE FROM accounts WHERE account_id = 101;

それでは、「ACCOUNTS_LOG」テーブルを確認してみます。
SELECT * FROM accounts_log
ORDER BY log_date;

accounts_log

たしかに、「ACCOUNTS_LOG」テーブルへの更新履歴が登録されています

ここで、SQL Serverの場合、DML トリガーステートメントでは、deleted テーブルおよび inserted テーブルという 2 つの特殊なテーブルが使用されます。そして、deleted テーブルには、DELETE ステートメントと UPDATE ステートメントの実行で影響を受けた行のコピーが格納され、inserted テーブルには、INSERT ステートメントおよび UPDATE ステートメントの実行で影響を受けた行のコピーが格納されるかたちになります。 つまり、UPDATEという更新処理は、DELETEの削除処理とそれに続くINSERTの挿入処理の組み合わせと考えることができ、deleted テーブルに古い行がコピーされ、その後、新しい行がinsertedテーブルにコピーされることになります。

そのため、「ACCOUNTS_LOG」テーブルへの更新履歴には、UPDATEに関しては、from deleted、from inserted からログを取得するようにトリガーを作っています。更新履歴にも、UPDATE処理により削除、登録の2つの行が確認できます。

 

今日は以上まで


にほんブログ村 IT技術ブログへ
にほんブログ村

1年の終わりに

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

IMG_2088_Original

今年もいよいよ残り2日。
会社も休みに入り、1年間を振り返り、反省する時期になりました。時間を取って1年を総括し、来年に向けてしっかりと準備していければと思いますが、今年は正月に実家にも帰って、成長した孫の姿を親に見せたいと思います。
写真は、会社で7月に行った、飯能市のムーミンバレーパークで撮った写真です。家族も連れて行ってもいいということで、子供たちも写真を撮りまくっていましたね。感謝です。

さて、今年のブログは、ほんの数回しか上げることができなかったのですが、データベーストリガーについて、参考になったという方がいましたので、次は早いうちにSQL Server版も取り上げていけたらと思っています。

来年も、皆様にとって良い年となりますように、心からお祈りいたします。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

照合順序(SQL Server)の異なるデータベース間のジョイン

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

 出雲大社

今年もあっという間にあと数日というところまで来てしまいました。今年の後半は仕事もかなり忙しくなり、なかなか時間も取れず、ブログの更新ができませんでしたが、気力を振り絞って書いています。
写真は、今年田舎に帰省した時に寄った出雲大社です。久しぶりに父親と一緒にお参りしたのですが、父親が子供のころは、正月に自転車で数時間をかけてお参りに行ったという話を聞き、今は車で1時間もかからずに行けるので、昔は大変だったんだなと改めて感じました。中を歩くと、旧暦の10月に全国から出雲大社に集う八百万の神様がお泊りになるという十九社という社があるのも初めて知りました。出雲地方以外、旧暦の10月のことを普通は神無月といいますが、出雲地方では神在月と言います。おもしろいですね。

さて、今年は元号が新しく令和になった記念すべき年でしたね。私としては、特にラグビーワールドカップでは、ONE TEAMという言葉がとても心に残りました。思い出せばいろいろなことがありましたが、大晦日に1年を振り返ってよかったと言えるように、最後まで頑張ろうと思います。皆さんも頑張りましょう!

<本日の題材>
照合順序(SQL Server)の異なるデータベース間のジョイン

SQL Serverの照合順序について、以前取り上げましたが、今回は、異なる照合順序のデータベース間でのデータのジョインをしたいケースが発生した場合に、可能なのかどうかについて確認してみました。これは、新しく作成したデータベースから、元々運用していた基幹のデータベースのテーブルを参照して抽出を行おうとしたら、照合順序が違うためにエラーが発生したという問合せを受けたことがあったので、自分でも一度試してみたいと思ったためです。照合順序とは、データの文字の大小関係を比較する場合の基準となるものをいうということで、前回説明しましたように、複数のテーブル間のジョインを行う際にも、重要な要素になります。

例)
照合順序が「Japanese_CI_AS」の既存のデータベース「BLOG」とは別に、「Japanese_90_BIN」という新しいデータベース「BLOG2」を作成します。

データベース作成時の照合順序設定

 ここで、元々「Japanese_CI_AS」の照合順序のデータベース「BLOG」にあるテーブル「BUMON_M」と同じレイアウトのテーブルを、新しく作成した照合順序が「Japanese_90_BIN」のデータベース「BLOG2」に作成して、データも登録します。

その後、「SYAIN_M」と「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

まず、「BLOG」データベース内でジョインする場合は、

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;

sql1

 上記を、「BLOG」データベースの「SYAIN_M」と先ほど作成した「BLOG2」データベースの「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

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

sqlエラー

上記の場合に正しく結果を出す方法は、データベースの照合順序を同じになるように、データベースを作り直すという方法もありますが、ジョインする項目について、COLLATE句を使用する方法があります。

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

ジョイン成功

エラーなく、結果が抽出されたことが確認できます。ただし、ジョインなどで必要な項目については全て、SQLの中でCOLLATE句の設定が必要になるため、面倒な部分もあります。

ちなみに、データベース自体の照合順序を変更する場合は、前回のときに取り上げたように、ただデータベースの照合順序を変更するだけではなく、既存のテーブルの各項目について、COLLATE文で照合順序を指定してALTER TABLE文を実行する必要がありますので、照合順序を正しく設定した空の新規データベースを作成した後に、テーブルの作成と、既存データの登録を行ったほうが早いケースもあると思います。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

データベーストリガー2(Oracle)

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

11216014_51

今年は、例年になく暑い5月でしたね。26日には北海道で5月としての全国の観測史上最高気温となる39・5度を観測するなど、異常な暑さでした。今からこの分だと、今年の夏はどうなるか?と心配してしまいます。

5月の中頃(5/19)はまだそれほどではなかったので、知人のご家族と一緒にバーベキューに行ってきましたが、天気も良く快適でした。
写真は埼玉県の羽生スカイスポーツ公園というところで、公園の名前にもあるように、スカイスポーツへの関心をもってもらおうという目的のもと整備された公園で、毎週土日祝日にはグライダーの飛行活動も行っていて、そこのクラブに入会すれば、実際にグライダーに乗ってスカイスポーツを楽しむことも出来るようです。

当日は、母の日も近かったので、日頃苦労している妻に感謝しようということで、準備は全てお父さんと子供たちで行って、妻には食べて楽しんでもらおうという企画で行いました。サプライズの花のプレゼントも用意して、妻もとても喜んでくれました。
たまにはごまをすっておかないと!

<本日の題材>
データベーストリガー2(Oracle)

ORACLEでのデータベーストリガーについて、前回取り上げましたが、前回は代表的なものとして、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動する「DMLトリガー」について取り上げてみました。
今回は、DDL操作(CREATE, ALTER, DROPなど)により起動する「DDLトリガー」について、試してみたいと思います。

例)
今回は、Oracleのデータベースについて、CREATE、DROP、ALTERという操作を行ったときに、どのユーザーが、どのオブジェクトに対して、いつ操作を行ったかをログに記録するというトリガーを作成してみます。
このとき、データベースに対してのトリガーを作成するためには、ADMINISTER DATABASE TRIGGER権限が必要です。
今回は、DBAロールを持つ、「test_admin」というユーザーでトリガーを作成します。

test_adminでログインした後、まず、処理のログを登録する「database_ddl_log」テーブルを作成します。

CREATE TABLE database_ddl_log (
user_name VARCHAR2(30),
activity VARCHAR2(20),
obj_name NVARCHAR2(30),
obj_type VARCHAR2(20),
event_date DATE
);

create_log

次に、トリガーを作成します。

CREATE OR REPLACE TRIGGER db_ddl_record
AFTER CREATE OR DROP OR ALTER ON DATABASE
DECLARE
   V_SYSEVENT   VARCHAR2(20);
   V_OBJ_OWNER   VARCHAR2(30);
   V_OBJ_NAME   VARCHAR2(30);
   V_OBJ_TYPE   VARCHAR2(20);
BEGIN
   V_SYSEVENT   := ora_sysevent;         -- イベントの取得
   V_OBJ_OWNER := ora_dict_obj_owner;   -- オブジェクトの所有者の取得
   V_OBJ_NAME   := ora_dict_obj_name;   -- オブジェクト名の取得
   V_OBJ_TYPE   := ora_dict_obj_type;   -- オブジェクトタイプの取得

INSERT INTO database_ddl_log
(user_name, activity, obj_name, obj_type, event_date)
VALUES (V_OBJ_OWNER, V_SYSEVENT, V_OBJ_NAME, V_OBJ_TYPE, SYSDATE);
END;
/

2行目に、AFTER CREATE OR DROP OR ALTER ON DATABASE と記載していますように、DATABASE全体において、CREATE、DROP、ALTER 文が実行された後に、起動するトリガーになります。そして、9行目から12行目までは、ora_ で始まるイベント属性関数を参照することで、必要な情報を一旦変数に格納し、最後にテーブルに登録します。

それでは、「BLOG_TEST」ユーザーでログインして、(1)新しくテーブルを作成、(2)定義の変更、(3)テーブルの削除をしてみます。

connect_blog_test

(1)   テーブルの作成
CREATE TABLE test_tab(
id INT
,name NVARCHAR2(30)
,CONSTRAINT PK_test_tab PRIMARY KEY (id));

cre_test_tab

(2)   テーブル定義の変更
ALTER TABLE test_tab
MODIFY name NVARCHAR2(50);

alter_tab

(3)   テーブルの削除
DROP TABLE test_tab;

drop_tab

それでは、上記の操作がログとして残っていることを確認します。
「test_admin」ユーザーで接続して、「database_ddl_log」テーブルを確認してみます。

connect_admin

SET LIN 120
COL user_name FORMAT A15
COL activity FORMAT A15
COL obj_name FORMAT A20
COL obj_type FORMAT A10
COL event_date FORMAT A20

SELECT
user_name, activity, obj_name, obj_type
, TO_CHAR(event_date, 'YYYY/MM/DD HH24:MI:SS') event_date
FROM database_ddl_log
ORDER BY event_date;

select_log

ログテーブルを抽出したところ、先ほどの処理(テーブル作成、テーブル定義変更、テーブル削除)が、ログの履歴として登録されていることが確認できました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

データベーストリガー(Oracle)

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

IMG_1978

桜の時期も終わってしまいました。ただ、今年は桜が咲いてから、少し寒かったので、桜がすぐには散らず、例年より長く、桜を楽しむことができたように思います。
東北や北海道などは、まだこれからがいよいよ本番ということなので、仕事で出張とかあればついでにお花見もできるのですが、そういう機会があるかな~。

<本日の題材>
データベーストリガー(Oracle)

データベーストリガーについて、まだブログで取り上げていなかったので、今回、取り上げてみたいと思います。
データベーストリガーとは、プロシージャやファンクションと同じように、データベースに格納されたPL/SQLのプログラムですが、プログラムなどから明示的にコールされて呼び出すのではなく、何らかのイベントをきっかけとして自動的に起動されるものです。

トリガーには、大きくは「DMLトリガー」「DDLトリガー」「その他」の3つの種類があります。
「DMLトリガー」は、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動するもの
「DDLトリガー」は、DDL操作(CREATE, ALTER, DROPなど)により起動するもの
「その他」については、
 ・データベースにログインやログオフするとき
 ・データベースの起動時や停止時
 ・エラーの発生時
などで起動するものがあります。
一般的によく使用される「DMLトリガー」についてですが、文トリガー、行トリガーという種類があり、さらに、処理の前後のどちらで起動させるかという、「BEFOREトリガー」「AFTERトリガー」という分類があります。

例)
今回は、ユーザー管理用の「ACCOUNTS」テーブルというものがあったとして、それに対しての、INSERT, UPDATE, DELETE が行われるタイミング(AFTER)で、処理のログを「ACCOUNTS_LOG」テーブルに登録するトリガーを作成してみます。

まず、処理のログを登録する「ACCOUNTS_LOG」テーブルを作成します。

CREATE TABLE accounts_log(
log_date DATE
,old_account_id INT
,new_account_id INT
,action VARCHAR2(50));

accounts_log

次に、トリガーを作成しますが、「ACCOUNTS」テーブルに対しての、INSERT, UPDATE, DELETE が行われてデータが更新されるたびに、「ACCOUNTS_LOG」テーブルに、処理日、変更前後の「ACCOUNT_ID」、そして処理内容を記録させます。
今回は、行トリガーのAFTERトリガーとして作成します。

CREATE OR REPLACE TRIGGER accounts_change_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON accounts FOR EACH ROW
DECLARE
  log_action  accounts_log.action%TYPE;
BEGIN
  IF INSERTING THEN
    log_action := 'Insert';
  ELSIF UPDATING THEN
    log_action := 'Update';
  ELSIF DELETING THEN
    log_action := 'Delete';
  ELSE
    DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
  END IF;

  INSERT INTO accounts_log (log_date, old_account_id, new_account_id, action)
    VALUES (SYSDATE, :OLD.account_id, :NEW.account_id, log_action);
END;
/

trigger

トリガーの中で、「FOR EACH ROW」を付けることで、行トリガーであることを示しています。そして、:OLD.列名、:NEW.列名とすることで、変更前と変更後の列名の値を取得することができます。
それでは、下記のように、「ACCOUNTS」テーブルへのデータの登録、修正、削除を順に行ってみます。

INSERT INTO accounts(account_id, name) VALUES(101, '山田 太郎');

INSERT INTO accounts(account_id, name) VALUES(102, '高橋 三郎');

UPDATE accounts SET NAME = '高橋 一郎' WHERE account_id = 102;

DELETE FROM accounts WHERE account_id = 101;

DML

それでは、「ACCOUNTS_LOG」テーブルを確認してみます。

「LOG_DATE」項目の表示を時刻まで出すようにフォーマットを変更します。
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

データを確認します。
SELECT * FROM accounts_log
ORDER BY log_date;

select_accounts_log2

たしかに、「ACCOUNTS_LOG」テーブルへの更新履歴が登録されています。
ここで、INSERTの場合は、変更前はデータがないので、old_accounti_id は NULL であり、DELETEの場合は、変更後はデータがなくなるので、new_accounti_id は NULLになることがわかります。

また、COMMITをする前にROLLBACKをしてみると「ACCOUNTS_LOG」のデータはどうなるでしょうか?

ROLLBACK;
rollback

再度、「ACCOUNTS_LOG」テーブルを確認してみます。

select_accounts_log_sai

先ほど見えていたデータは、処理が取り消されたので、ログからも取り消されているのが確認できます。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

データベースリンク(Oracle)

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

IMG_1878

だいぶ前になってしまいますが、お正月に、田舎に帰省したときに、飛行機から富士山が綺麗に見えたため、撮った写真です。飛行機の搭乗員の方も、これほど富士山が綺麗に見えるのは珍しいと言われていましたので、とても得をした気分になりましたね。
今年は縁起がいいかも!
いろいろと仕事が忙しかったため、前回の投稿からかなり時間が空いてしまいましたが、今年も頑張っていければと思います。よろしくお願いいたします。

<本日の題材>
データベースリンク(Oracle

システム開発の仕事をしていると、時折、複数のORACLEのサーバーがあり、それぞれに格納されているテーブルを同時にアクセスしてデータを抽出するなどの処理を行いたいという依頼を受けることがあります。
こういうときにORACLEデータベース側で、他のデータベースにアクセスできるデータベースリンクというものを作成することで、そういった操作が可能になります。
今回は、それを試してみたいと思います。

例)
今回は、Oracle12cのデータベースから、Oracle11gR2 のデータベースへのデータベースリンクを作成して、Oracle12c側からOracl11gR2側のデータを抽出できるようにしたいと思います。

設定の内容としては、大きくは以下になります。
①    ORACLEデータベースサーバー同士で接続できるように、
  ネットサービス名を設定します。
  今回は、Oracle12c側で設定を行います。
②    データベースリンクを作成するための権限を、該当のユーザ
  ーに与えます。
③ 該当のユーザーでデータベースリンクを作成します。

それでは始めて行きます。
①ネットサービス名の設定は、ツールでも可能ですが、今回はOracle12c側の「tnsnames.ora」ファイルを直接編集することで行います。

追加するネットサービス名を「ORCL11G」として、以下の内容を追加します。

ORCL11G =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = サービス名)
   )
 )

※「HOST=」の後には、Oracl11gR2サーバーのIPアドレス(ホスト名でもよい)を設定し、「PORT=」の後にはデフォルトのポート番号を記載しています。最後の「SERVICE_NAME =」の後には、Oracle11gR2データベースのサービス名を設定します。

②    データベースリンクを作成するための権限を、該当のユーザーに与えます。以前、「Oracle12cでのユーザー作成でエラー「ORA-65096」」という内容でブログで取り上げたときの内容を使って、Oracle12cの「BLOG_TEST」というユーザーに権限を与えます。

Sqlplusでsysdba でログインした後、「PDBORCL」というプラガブルデータベースに接続します。

ALTER SESSION SET CONTAINER = PDBORCL;

alter_session

 BLOG_TEST というユーザーでデータベースリンクを作成したいのですが、データベースリンク作成には権限が必要なので、このユーザーに権限を与えます。

GRANT CREATE DATABASE LINK TO BLOG_TEST;

cre_databaselink

それでは、「BLOG_TEST」ユーザーに接続して、データベースリンクを作成します。

CONNECT BLOG_TEST/passwd@PDBORCL
(passwdのところは、パスワード)
connect_2

Oracle11gR2のデータベースに対するデータベースリンク「ORCL11DB」を作成します。

CREATE DATABASE LINK ORCL11DB CONNECT TO BLOG_TEST IDENTIFIED BY passwd USING ‘ORCL11G’;

cre_databaselink_2
(IDENTIFIED BY の後には、Oracle11gR2側のBLOG_TESTユーザーのパスワードを設定します。)

 データベースリンクが作成されましたので、Oracle11gR2側の「CUSTOMER」テーブルのデータを抽出してみます。

SELECT * FROM CUSTOMER@ORCL11DB;

select_dblink

毎回、「CUSTOMER@ORCL11DB」のように書くのが面倒だという場合などには、シノニムを作成することもできます。

まず、シノニムを作成できる権限を、「BLOG_TEST」に与えます。一度、SYS ユーザーに戻ってから下記コマンドを実行します。

GRANT CREATE SYNONYM TO BLOG_TEST;
grant_cre_synonym

再度BLOG_TEST で接続して、シノニムを作成してみます。

CONNECT BLOG_TEST/passwd@PDBORCL
connect_2

CREATE SYNONYM OR11_CUSTOMER FOR CUSTOMER@ORCL11DB;

cre_synonym

それでは、シノニムを使って、データベースリンクを貼ったOracle11gR2側データベースの「CUSTOMER」データを抽出します。

SELECT * FROM OR11_CUSTOMER;
select_synonym

シノニムを使って、データベースリンクを貼ったテーブルのデータを抽出できることが確認されました。

ただし、注意点として、リモートのデータベースへ接続するため、ネットワークの状態や、データ件数やデータを取得するやり方により、ローカルのデータベース内のみでの操作に比べて、パフォーマンスが低下する場合があります。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村