全148件 (148件中 1-50件目)
-
MCSE : Data Platform
土曜日に70-459を受けに行って首尾よく合格し、MCSE:Data Platformへのアップグレードに成功しました。残るは70-460に合格して、MCSE:Business Intelligenceへのアップグレードも達成することですが・・。さすがにこれは容易ではないので、しばらくSSAS/SSRSを触り倒して思い出します（MDXもね）。
2013.02.19
コメント(0)
-
MCSA - SQL Server 2012
70-457を11月に、70-458を先日合格して、ようやくMCSA-SQL Server 2012へアップグレードできた。MCSEにアップグレードするにはまだ試験を受けないといけないという。。今回の試験は、BIと無縁のDBA畑の人はかなり要注意だと思います。特に70-458後半（新規の場合の70-463）。SQL Server 2000/2005/2008と、Database Administratorとしての資格はSSISのことを一切知らなくっても合格できました(データベース管理とBI開発は別資格だったため）。でも、SQL Server 2012では、MCSAが共通資格（データベース管理＋BI開発）になっているため、絶対にSSISを避けて通ることができなくなったんです。そのSSISの試験がとにかく細かい！2005時代の70-445や2008時代の70-448と同じで、ソリューションではなく、設定を問う問題がメインなのです。とにかく、すべてのタスクと機能を触っておきましょう。
2013.02.04
コメント(0)
-
SQL Server AlwaysOn
AlwaysOnはSQL Server 2012の最大のウリなのだが、データベースミラーリングとは違い、１台のテストPCでは試すことすらできない。○前提条件・Windows Server 2008 R2 SP1以上・同一Active Direcotoryに参加していること・WSFC(Windows Server Failover Cluster)のノードであること※WSFCを使用する時点で、OSはEnterprise版またはDatacenter版が必須、 またSQL Server 2012もEnterprise版が必須。 この仕組みを利用する以上は、その程度のコストは当然か。 SQL Serverは名前付インスタンスをサポートせず、１台１ノードとなる。・サービスアカウントはドメインユーザ（共通） ・データベースフォルダは全台同じパス。初期バックアップ保管用の共有フォルダを作成 （いずれもドメインユーザには更新権限が必要）・対象DBの復旧モードはフルで、完全バックアップ取得済・サービスでAlwaysOnを有効化していること○セットアップ・WSFCの設定については、以下のページに詳しいので、参考にさせていただいた。 http://yama30501.blog137.fc2.com/blog-entry-30.html 手順をまとめると以下の通り ※ActiveDirectory配下にDC以外のサーバを3台以上準備 a) 全台でサーバマネージャから「フェールオーバークラスタリング」をインストール b) プライマリより「フェールオーバークラスターマネージャー」から「構成の検証..」を実施 ・サーバを追加し、「すべてのテストを実行する」 c) プライマリより「フェールオーバークラスターマネージャー」から「クラスターの作成..」を実施 ・サーバを追加し、クラスタ名とクラスタ用のIPアドレスを指定 ・プライマリで作成すれば、ほかのサーバでは「追加」できる d) それぞれのサーバにSQL Server 2012をインストール ・サービスアカウントをドメインユーザとする（インストール後の変更も可） ・全台に同じデータベースフォルダを作成する（変更権限付与） ・プライマリサーバに共有フォルダを作成する（変更権限付与） ・プライマリサーバでデータベースを作成（作成済データベースフォルダに） ・復旧モードを完全にして、共有フォルダにフルバックアップを取得 e) AlwaysOnを有効化する（構成マネージャより・要サービス再起動） f) Availability Groupの作成 ・セカンダリについて、自動フェールオーバーは１台、同期モードは２台、全台で４台まで ・RTMでは、AG作成時に静的IPのリスナー作成が可能になっている ・最初のデータ同期に共有フォルダを指定○AlwaysOnの検証 ・フェールオーバーは、SSMSならばプライマリからも可能で、対象のセカンダリを選べる （内部的にはsqlcmdモードでスクリプトを生成しており、セカンダリに接続して、コマンド実行を行う） ALTER AVAILABILITY GROUP ... FAILOVERのコマンドはセカンダリのみで受け付ける ・同期モードの場合は「同期済み」、非同期モードの場合は「同期中」と表示される ・セカンダリのバックアップには「WITH COPY_ONLY」が必須 ・手動フェールオーバーで、非同期のサーバをプライマリにする場合は、 ALTER AVAILABILITY GROUP (AG名) FAILOVER FORCE_FAILOVER_ALLOW_DATA_LOSS この場合、他のサーバは手動で同期を再開させる必要あり （それまでは「同期されていません」と表示され、読取アクセスもできない） ALTER DATABASE (DB名) SET HADR RESUME なお、非同期のサーバがプライマリになっている間は、当然すべてが非同期モードとなる
2012.06.30
コメント(0)
-
SQL Server 2012 Express LocalDB
SQL Server Express Locadb - SQL Server Expressの簡易バージョン(開発者向け)・インスタンス：(localdb)\v11.0・データベースファイルはC:\User\(ユーザ名)配下に作成される・ユーザインスタンスでの接続をサポート・インスタンス照合順序：SQL_Latin1_General_CP1_CI_ASで変更できない・ファイルストリームをサポートしない・マージレプリケーション／サービスブローカに制約あり・接続：sqlcmd -S(localdb)\インスタンス名 -E※SqlLocalDBユーティリティ LocalDBインスタンスの作成/開始 sqllocaldb create "インスタンス名" 11.0 -s LocalDBインスタンス情報の照会 sqllocaldb info "インスタンス名" LocalDBインスタンスの停止 sqllocaldb stop "インスタンス名" LocalDBインスタンスの作成/開始 sqllocaldb delete "インスタンス名"
2012.06.17
コメント(0)
-
SSDT(SQL Server Data Tools)
SSDT(SQL Server Data Tools)は、開発者向けのデータベースツール（Visual Studio 2010のコンポーネントとして使用）SQL Server 2012をインストールすると、"SQL Server Data Tools"の最新版がインストールされたようにメニューに表示されているが、実際は「Web PIでのダウンロード・インストールが必要」※提供されている主な機能・テーブルの作成：スクリプトとデザインビューが相互反映する（すばらしい）。しかし、外部キー追加などはデザイン側ではできなさそう・スキーマ比較（データ比較）：テーブル再構築の場合、SSMSでは、「Tmp_テーブル名」を作成してリネームする。すでに「Tmp_テーブル名」が存在すれば「Tmp_ct_user_role_1」のように連番をつけて競合を回避する。SSDTでは「tmp_ms_xx_ct_user_role」を作成するが、すでに存在すると実行エラーとなる。・リファクタリング：この機能を使う場合は、一旦データベースプロジェクトを作成する必要あり。実行するとスキーマがコピーされたデータベースプロジェクトが作成される（その過程でLocalDBインスタンスが作成される）そのプロジェクト内で「リファクタリング」（テーブル名・スキーマ変更）などを実施した後、「スキーマ比較」により本番へ反映させる。なお、ストアドプロシージャは警告レベルならば作成できるが、以下の例では「変数またはパラメーター "@@SERVERNAME" を宣言する必要があります」というエラーになり作成できないCREATE PROCEDURE [dbo].[Procedure]ASEXEC ('SELECT * FROM ['+@@SERVERNAME+'].[tempdb].[dbo].[Table1]')
2012.06.17
コメント(0)
-
分析関数のウィンドウフレーム
SQL Sever 2012ではついにOVER句のウィンドウフレームがサポートされた。SQL Server 2008までの議論では、セットベース（一括処理）・カーソルベース（順次処理）の２通りのアプローチのうち、後者がパフォーマンスで上回る可能性があるのは、累計算出のケースのみだとされてきた。それもSQL Server 2008ではウィンドウフレームをサポートしていなかったからで、SQL Server 2012ではさらにカーソルへの依存を低下させることができると思われる。（Oracleがとうの昔に対応済であることを考えると、遅きに失した感はあるが・・）・SUM/AVG/COUNTなどの集計関数の場合でOrder句のみを記述した場合、「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」が省略されているとみなされる(ROWSではなくRANGE)。 また短い構文として「ROWS/RANGE (PRECEDING句)」は認められ、「ROWS/RANGE BETWEEN (PRECEDING句) AND CURRENT ROW」に読み替えられる。 つまり、以下の記述は同じ意味となる SUM(AMT) OVER (PARTITON BY KEY1 ORDER BY KEY2) SUM(AMT) OVER (PARTITON BY KEY1 ORDER BY KEY2 RANGE UNBOUNDEND PRECEDING) SUM(AMT) OVER (PARTITON BY KEY1 ORDER BY KEY2 RANGE BETWEEN UNBOUNDEND PRECEDING AND CURRENT ROW) 以下の記述はNG SUM(AMT) OVER (PARTITON BY KEY1 ORDER BY KEY2 RANGE UNBOUNDEND FOLLOWING) # PRECEDING句ではないためだが、Oracleでは指定可能な構文・ROWS/RANGEの違いは、物理的に集計するか（ROW)、論理的に集計するか(RANGE)だが、 Order句に指定したキーがユニークであれば（＝CURRENT ROWと同じ値が常に１行の場合）、結果に差は生じない・RANGE句はUNBOUNDED PRECEDING/FOLLOWINGとCURRENT ROWと一緒にしか使えない 「RANGE BETWEEN 2 PRECEDING AND CURRENT ROW」などはNG （これもOracleとは異なる） 「メッセージ 4194、レベル 16、状態 1、行 2 RANGE は、UNBOUNDED および CURRENT ROW ウィンドウ フレーム区切り記号でのみサポートされます。」・PRECEDING/FOLLOWINGに数値を指定する場合、変数は使用できない（Oracleとは異なる）・ROW_NUMBER／DENSE_RANK/RANKなどの順位付け関数、LAG/LEADなどの分析関数ではウィンドウフレームを指定できない 「メッセージ 10752、レベル 15、状態 3、行 4 関数 'DENSE_RANK' にウィンドウ フレームを指定することはできません。」・CLR集計関数にウィンドウフレームを指定できない（Over句は可能）
2012.06.09
コメント(0)
-
列ストアインデックス
列ストアインデックス（カラムストアインデックス）は、主にDWHでの集計効率を向上させるために導入された列集約形式のインデックス。CREATE NONCLUSTERED COLUMNSTORE INDEX [CIX_1] ON TABLE1 (F1)※制約も多い・使用できないデータ型： binary, varbinary, image, text, ntext, varchar(MAX), nvarchar(MAX), timestamp, uniqueidentifier, sqlvariant, xml, 有効桁数が18桁より多いnumeric(decimal)など・クラスタ化できない・複数作成できない・ビューには張れない・計算列やスパース列、ファイルストリーム列は含められない・レプリケーション、CDC、CDTとは同時に使用できない※列ストアインデックスがあるテーブルは更新できない・更新する場合は、一旦無効にするか、パーティションを使用 ALTER INDEX [CIX_1] ON [TABLE1] DISABLE （更新） ALTER INDEX [CIX_1] ON [TABLE1] REBUILDなお、今後のバージョンでもテーブルが更新不可になるかどうかは未定「テーブルを読み取り専用にするためのメカニズムとして列ストアインデックスを作成しないでください」と記載あり※その他・ベーステーブルのクラスタ化インデックスの列は、指定しなくても列ストアインデックスに自動的に含まれる・列ストアインデックスは作成時に独自のアルゴリズムで圧縮済・クエリヒント：IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX・SSMSの場合、ユーザ定義型はGUIでは指定できない （ユーザ定義型でも、base_typeが使用可能なデータ型であれば、列ストアインデックスを設定できる） 逆に有効桁数が18桁より多いnumericは指定できるが実行時にエラーとなる
2012.06.02
コメント(0)
-
データベースミラーリングの廃止予定
SQL Server 2012で廃止された機能には特に大きなものはないが、「今後のバージョンでサポートされない機能」には、「データベース ミラーリング」と書かれている。代替は「AlwaysOn 可用性グループ」だが、「使用しているエディションの SQL Server で AlwaysOn 可用性グループ がサポートされていない場合は、ログ配布を使用してください」AlwaysOnを使うためには、WSFC(Windows Server FailOver Cluster)が必要なので、・Windows Server 2008 R2 SP1のEnterprise / Datacenter Edition・SQL Server 2012のEnterprise Editionの組み合わせでしか利用できない。一方データベースミラーリングは同期型であれば、Standard Editionでもサポートされていたので、コストの問題でAlwaysOnを導入できない状況は十分に考えられる。「その場合はログ配布にせよ」というのは殺生な話だと思うのだが・・・。SQL Server 2008以降、新機能はほとんどがEnterprise向けになっているのは悲しいことではある。
2012.06.02
コメント(0)
-
包含データベース
SQL Server 2012に関する機能チェックの簡単なまとめを記載していく。包含データベース(CDB：Contained database)は、SQL Server 2012で使えるようになった機能。データベース内にパスワード付ユーザを作成することで、ログインとの紐付けがなくてもアクセスできるように設定でき、データベースの別インスタンスへの移行時にログインのマッピングをやり直さなくてもよくなるのがメリットのようだ。（もう一つは、一時テーブルの照合順序がtempdbではなく、包含データベースに依存するようになる点）※sp_configureでCDB認証を許可する必要あり※user optionはゼロでなくてはならない（新規）CREATE DATABASE db1 CONTAINMENT=PARTIAL（変更）ALTER DATABASE db1 SET CONTAINMENT=PARTIAL（新規）CREATE USER usr1 WITH PASSWORD='xxxxxx'（移行）sp_migrate_user_to_contained N'usr1',N'keep_name',N'disable_login'usr1というログインがない状態でも、INITIAL CATALOG=db1を指定することでusr1は、db1にアクセスできる(master/tempdbの一部もアクセス可能)※要注意点・レプリケーション、CDC、CDTの機能をサポートしない・CLRをサポートしない・dboのユーザの場合、包含状態の変更もデータベースユーザの作成も可能なので、CDB認証を許可することで、管理者が知らないうちにContained Databaseに変更され、ユーザが作られ、脆弱なパスワードでデータベースにアクセスされるというようなことが起こりうる（インスタンスの管理者にとってはセキュリティの低下を招きかねない）※その他の情報・包含データベースをリストア／アタッチした場合に、データベースをRESTRICTED_USERモードにすると、データベースユーザでの接続はできなくなる・非包含になっている可能性があるオブジェクトの一覧をdm_db_uncontained_entitiesで確認できる・ユーザデータと一時データの照合順序はDATABASE_DEFAULTに依存するが、メタデータについてはCATALG_DEFAULT(=Latin1_General_100_CI_AS_WS_KS_SC)となり、変更できない
2012.06.02
コメント(1)
-
SQL Server 2005 トラック終了
SQL Server 2005のメインストリームサポート終了が2011/4/12。ということで、2005トラックの試験は6/30で終了するそうです。http://www.microsoft.com/japan/learning/mcp/exam_endlist.htmメインストリームサポートが終了すると、MCTSもMCITPも失効するはずですが、2005トラックは新資格体系になって初だから過去に失効した例はありません。（2000トラックのMCDBAは失効しない）果たして、いつ、本当に失効するのか、ちょっと見ものだと思ってます。
2011.03.23
コメント(0)
-
sp_spaceused
データベースの空き容量を確認するsp_spaceusedというシステムストアドプロシージャは、昔からあるため、今も利用されることが多い。しかし、このストアドプロシージャにはある特殊性があり、データベースプログラミングでの使用が大変難しい。それはこのストアドが「二つのレコードセットを返すから」。しかも、一つ目は三列、二つ目は四列返る。ちなみにテーブルのサイズを見る場合は一つのレコードセットしか返さないので、問題はない。例えばインスタンス内にある全てのデータベースの空き情報を収集しようとして、カーソルループ処理を書いたとしても、INSERT INTO...EXEC文は複数のレコードセットを受け取れず、エラーになる。データベースの外であればマルチレコードセットを受け取ることも出来なくはないが、データベース内で完結させるためには、止むを得ず「sp_helptextでソースを分析し、必要なところを抜粋して使う」という手段を使わざるを得なかった(と思う)。なぜこの様な使いづらい結果の返し方をするのか、はともかく、何か手段はないか考えてみる。複数のレコードセットを同時に受けることは、Transact-SQLでは基本的にどうやっても難しい。しかし、複数のSELECT文を返す場合、先頭の一つだけを拾う方法ならばある。SELECT * FROM OPENROWSET('SQLNCLI','Database=(local);Trusted_Connection=Yes;Database=tested','EXEC testsp')これならば、一つ目のレコードセットだけでもワークに受けることができる。しかし、このクエリはsp_spaceusedでは成功しない。sp_spaceusedは、中で条件分岐があり、戻りのデータ型がそれにより異なるため、戻り値のデータ型を特定出来なかったというエラーが出てしまうのだ。ただ、現在CTP1が公開されているSQL Server Denaliであれば、エラーにしない方法がある。SELECT * FROM OPENROWSET('SQLNCLI11','Database=(local);Trusted_Connection=Yes;Database=tested','EXEC sp_spaceused WITH RESULT SETS ((F1 sysname,F2 sysname,F3 sys name),(F1 sysname,F2 sysname,F3 sysname))ここまでやってレコードセット一つというのも、微妙だが。。
2011.03.21
コメント(0)
-
マトリックス系クエリをSQL Serverで
CREATE TABLE Trans(BASEDATE datetime NOT NULL,BUMONCD int NOT NULL,CATCD int NOT NULL,MVMT money NOT NULL)GOALTER TABLE Trans ADD CONSTRAINT [PK_1] PRIMARY KEY CLUSTERED (BASEDATE,BUMONCD,CATCD)GO列に部門、行に科目というマトリックス計表を作ってほしいと言われるとする。本当はそんな結果を返すのは、本来のSQLの仕事ではないし、最初からそのようなソリューションがないDBMSも多い。だから、アプリケーションか、Excelのピボットテーブルでも使って実現するのが普通かもしれない。それを敢えてクエリにこだわってやってみる。まず、通常は動的クエリを使って書く。○パターンＡ（GROUP BY形式のクエリを組み立て）DECLARE @sql varchar(MAX)SELECT @sql=ISNULL(@sql+',','')+'SUM(CASE WHEN BUMONCD='+CONVERT(varchar,BUMONCD)+' THEN MVMT END) ['+CONVERT(varchar,BUMONCD)+']'FROM TransGROUP BY BUMONCDORDER BY BUMONCDSET @sql='SELECT CATCD,'+@sql+' FROM Trans GROUP BY CATCD ORDER BY CATCD'EXEC (@sql)○パターンＢ（PIVOT式を組み立て）DECLARE @sql varchar(MAX)SELECT @sql=ISNULL(@sql+',','')+'['+CONVERT(varchar,BUMONCD)+']'FROM TransGROUP BY BUMONCDORDER BY BUMONCDSET @sql='SELECT * FROM (SELECT BUMONCD,CATCD,MVMT FROM Trans) p1PIVOT (SUM(MVMT) FOR BUMONCD IN ('+@sql+')) AS p2ORDER BY p2.CATCD'EXEC (@sql)○パターンＣ（Accessを利用する）ちょっとマニアックな方法も試してみる（ちょっと面倒くさいけど）。Accessには「クロス集計クエリ」というものがある。これはまさにクエリの結果をマトリックスで返してくれるので、これを利用してみる。a) Accessファイルを１つ作成し、上記テーブルをリンクする（ODBCのDNSが必要。。）b) SQL Server側にリンクサーバを設定 （Microsoft.Jet.OLEDB.4.0使用。RPC出力は有効にすること） リンクサーバ名は「ACC」c) クエリを実行EXEC ('TRANSFORMSUM(MVMT) AS TOTALSELECT dbo_Trans.[CATCD]FROM dbo_TransGROUP BY CATCDPIVOT BUMONCD;') AT ACC○パターンＤ（SSASを利用する）もともとクロス集計は性質的には多次元データベースの方が向いている。なので、SSASを利用してみる。a) BIDSからAnalysis Serviceプロジェクトを作成し、データソース、データソースビュー、キューブ、ディメンションを設定b) SSASに配置して処理を実行c) SQL Server側にリンクサーバを設定 （Microsoft.OLEDB Provider for Analysis Service 9.0/10.0を使用。RPC出力は有効にすること） リンクサーバ名は「OLAP」d) クエリを実行EXEC ('SELECTBUMONCD.CHILDREN ON 0,CATCD.CHILDREN ON 1FROM Trans;') AT OLAPここまで試す人もあまりいないかもしれないが、いくつかおもしろいことが分かる。○パターンＣの場合・Accessのリンクテーブルに対してクロス集計クエリを実行する場合、AccessはJet経由で以下のようなクエリを投げる 「SELECT BUMONCD,CATCD,SUM(MVMT) FROM Trans GROUP BY BUMONCD,CATCD」 その上で、グルーピングされた結果をクロス集計のレイアウトに落としているので、このクエリは意外とレスポンスは速い・同時実行はできないが、同時実行しても壊れることはない（順には処理される）○パターンＤの場合・SSMSの場合、実行するクエリはSQLではなく、MDXになる。 SSMSでMDX専用のエディタを使うと、列側が複数あってもきちんと表示されるのだが、MSOLAPを使ってリンクサーバ経由で呼ぶと、 見出しは、[BUMONCD].[BUMONCD].&[11]と言った形に無理やり表現される。・もちろん、出来上がったキューブは処理しないとデータが反映しない（プロアクティブキャッシュはEnterpriseのみ）
2010.07.02
コメント(0)
-
PL/SQL Gold合格
初ピアソンだった。いつも行っている試験会場は同じところでプロメとピアソンを両方受けられるので非常に助かる。会場によってはいろいろ苦労するみたいだが、トラブルもなく、画面がおかしくなることもなく、快適に受験できた。結果は合格。今は仕事でPL/SQLをやる機会はないのが残念だが、まあいいでしょう。
2010.06.05
コメント(0)
-
Set-based vs Cursor-based
データベースプログラミングについては、基本的に２つのアプローチがある。SQLを駆使して処理するアプローチと、カーソルやプログラムを駆使して順次処理を行うアプローチだ。前者をセット型処理（Set-based)、後者を手続き型処理またはカーソル型処理(Procedure-bases/Cursor-based)と呼ぶことがある。○Oracleは構造的にProcedure-based処理向きPL/SQLがデータベースエンジンとは別のPL/SQLエンジンで実行される以上、カーソルを前提とした処理になるのは仕方がない。PL/SQLのストアドプロシージャ内でSELECTを書いても結果は返ってこないし、PL/SQLで結果を受けとった瞬間にそれはテーブルではなく配列になってしまう。その分カーソル機能は充実しており、SQL(Set-based)とPL/SQL(Cursor-based)は状況により使い分けるべき、ということになっている。例えば、以下のような相関更新の場合は、PL/SQLの方が向いている（＝処理速度が速い）そうだ。--SQL(Set-based)UPDATE cust1 c SET total=(SELECT SUM(s.amount*p.price) FROM sales s,product p WHERE s.cust_id=c.cust_id AND s.prod_id=p.prod_id)WHERE cust_id>30000;--PL/SQL(Cursor-based)DECLARE CURSOR s_cur IS SELECT cust_id,SUM(s.amount*p.price) total FROM sales s,product p WHERE s.prod_id=p.prod_id GROUP BY c.cust_id;BEGIN FOR rec IN s_cur LOOP UPDATE cust1 SET total=rec.total WHERE cust_id=rec.cust_id AND cust_id>30000; END LOOP;END;○SQL ServerはSet-based処理向き前述の処理をSQL Serverで考えた場合、実は圧倒的にSet-basedの方が速い。--SQL(Set-based)UPDATE cust1SET total=(SELECT SUM(s.amount*p.price) FROM sales s,product p WHERE s.cust_id=c.cust_id AND s.prod_id=p.prod_id)WHERE cust_id>30000;--Transact-SQL(Cursor-based)DECLARE s_cur CURSOR FAST_FORWARD LOCAL FOR SELECT cust_id,SUM(s.amount*p.price) total FROM sales s,product p WHERE s.prod_id=p.prod_id GROUP BY c.cust_idDECLARE @cust_id intDECLARE @total moneyOPEN s_curFETCH NEXT FROM s_cur INTO @cust_id,@totalWHILE (@@FETCH_STATUS=0)BEGIN UPDATE cust1 SET total=@total WHERE cust_id=@cust_id AND cust_id>30000 FETCH NEXT FROM s_cur INTO @cust_id,@totalENDCLOSE s_curDEALLOCATE s_curこれは、２つのDBMSにおける相関更新の扱いの違いによるものと思う。相関更新の場合、1行につき1回サブクエリが実行される構造であるため、どうしても処理が重くなる。OracleではSQLによる更新はこの形式でなくては行えないので、カーソルの方が速いという結論になりうる。しかし、SQL Serverの場合は、以下のような結合更新が可能であり、相関更新はオプティマイザにより内部的に書き換えられてしまう。UPDATE cust1SET total=x.totalFROM cust1 cINNER JOIN(SELECT s.cust_id,SUM(s.amount*p.price) total FROM sales s,product p WHERE s.prod_id=p.prod_id GROUP BY s.cust_id) x ON x.cust_id=c.cust_idWHERE c.cust_id>30000よく見てみると、やっていることはOracleのPL/SQLと極めて近く、これをSQLで行えるから速いと言えるわけだ。実際にSQL Serverを使っていると、テーブル変数も活用できるし、徹底してSet-basedで処理を書くことが可能で、カーソルを使わざるを得ないケースというのは非常に少ない。○SQL Serverでもカーソルの方が速いケースSQL Serverでも「カーソルの方が速い」ケースというのは存在する。その代表的なものが「累計算出」、つまり複数行の結果に対し、累計を表示するケースだ。顧客ID 日付 金額 累計0001 4/1 100 1000001 4/2 200 3000001 4/3 150 4500001 4/4 300 7500002 4/2 100 1000002 4/3 400 5000003 4/1 200 2000003 4/4 200 400このような場合、基本的に相関サブクエリを結合更新に書き換えることはできず、処理する行数が幾何級数的に増加することになる。各顧客の行数が多くなればなるほど、カーソル処理の方が有利になる。でも、OracleではおそらくSet-basedの方が速い。以下のような分析関数が使えるからだ。SUM(累計) OVER (PARTITION BY 顧客ID ORDER BY 日付 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)残念ながら、SQL Serverは2008でも、集計関数のOVER句に「ORDER BY」を使うことができない。逆にいうと、このケースは「SQL ServerにANSIでも定められている必要な機能がないから、カーソルの方が速い」とも言えるわけだ。これが実装されればカーソルの方が速いというケースは殆どなくなると思われる。
2010.05.30
コメント(0)
-
Oracle/SQL ServerのLOBの入出力
データベースでLOB（ラージオブジェクト）を管理するのは面倒くさいとよく言われる。実際に行う場合、入出力は基本的にJavaや.Netなどのプログラムに頼るのを前提に考える場合が多いだろう。ただ、Oracle/SQL Serverではデータベース側で直接扱う方法も提供されている。特にSQL Serverの場合は、うまく使う方法を紹介している例が少ないので、紹介したいと思う。SQL Server 2000までは、text／imageの２タイプしかなく、取り扱いも面倒だったが、SQL Server 2005からはvarchar(MAX)／varbinary(MAX)が導入されたことで、通常のvarchar型／varbinary型とほぼ同様に扱うことができるようになった。この改善は正直大きい。1)ファイルを格納する(Oracle) Oracleの場合、LOBは明らかに通常のデータ型と別物として取り扱われるため、その操作にはロケータを介する必要があり、Oracleが提供しているDBMS_LOBパッケージを使うことになる。一般的なINSERT手順だと以下の通りとなるが、ステップが多くていかにも使いづらい。（なお、あらかじめ対象パスをCREATE DIRECTORYして権限を付与しておくこと） a) 空のBLOBをINSERTして、BLOBロケータを取得 b) BFILENAMEでBFILEロケータを取得 c) DBMS_LOB.FILEOPENでファイルをオープン d) DBMS_LOB.GETLENGTHでファイルサイズを取得 e) BFILEロケータ、BLOBロケータ、ファイルサイズを使ってDBMS_LOB.LOADFROMFILEを実行 f) DBMS_LOB.CLOSEでファイルをクローズ DECLARE v_blob BLOB; v_floc BFILE := BFILENAME('PATH_TMP','Sample.jpg'); v_size PLS_INTEGER; BEGIN INSERT INTO BINARYTABLE(seq,bin) VALUES (1,EMPTY_BLOB()) RETURNING BIN INTO v_blob; DBMS_LOB.FILEOPEN(v_floc); v_size := DBMS_LOB.GETLENGTH(v_floc); DBMS_LOB.LOADFROMFILE(v_blob,v_floc,v_size); DBMS_LOB.CLOSE(v_floc); END なお、SQL*Loaderを使えば、INSERT限定だが一括読み込みを実行することができる。 こんなコントロールファイルを作成すればよい。 複数ファイルでも一括で読み込めるから、単体の作業としては、便利だろう。 LOAD DATA INFILE blob.dat APPEND INTO TABLE BINARYTABLE FIELDS TERMINATED BY "," (seq, filename FILLER char, filedata LOBFILE( filename ) TERMINATED BY EOF)2)ファイルを格納する(SQL Server) SQL Serverの場合は、相当シンプルで基本的には以下の１文で取り込める。 INSERT INTO BINARYTABLE(seq,bin) SELECT 1,x.* FROM OPENROWSET(BULK 'C:\TEMP\Sample.jpg',SINGLE_BLOB) x3)ファイルを出力する(Oracle) Oracleの場合、DBMS_LOBパッケージとUTL_FILEパッケージの両方を使って、32767バイトずつループして書き出すことになる。これも随分プログラム的な操作になる。PL/SQLもプログラミングだなと実感するケースではある。 a) 対象データを検索して、BLOBロケータを取得 b) DBMS_LOB.GETLENGTHでサイズを取得 c) UTL_FILE.FOPENで出力ファイルをオープン d) DBMS_LOB.READで読み出し、UTL_FILE.PUT_RAWでファイルに書きだす（繰り返し） e) UTL_FILE.FFLUSHでバッファをフラッシュする f) UTL_FILE.FCLOSEでファイルを閉じる （長くなるのでソースは省略）4)ファイルを出力する(SQL Server) SQL Serverの場合、ファイルを格納するときのような方法はないが、bcpユーティリティを利用する方法がある。これをTransact-SQLから実行するには、xp_cmdshellを使う。 注意すべきはプレフィックス長の指定で、VARBINARYの場合は8バイトのプレフィックスが出力されてしまうことだ、そのままだと出力できても開けないことが多い。 したがって、プレフィックス長を0に指定するために、フォーマットファイルを利用する。 フォーマットファイルは一旦手動でBCPを実行することにより、生成することができる。 C:\TEMP>bcp "SELECT BIN FROM DB1.dbo.BINARYTABLE WHERE SEQ=1" QUERYOUT "C:\TEMP\Sample.jpg" -T フィールド BIN [varbinary(max)] のファイル ストレージ型を入力してください（Enter） フィールド BIN [8] のプレフィックス長を入力してください0（Enter） フィールド BIN [0] の長さを入力してください（Enter） フィールド ターミネータ [none] を入力してください（Enter） このフォーマットファイル情報をファイルに保存しますか[y/n]y（Enter） ホストファイル名[bcp.fmt]fmt.fmt（Enter） これにより作成されたフォーマットファイル(fmt.fmt)を使ってBCPを実行する。 DECLARE @cmd varchar(300) SET @cmd='BCP "SELECT BIN FROM DB1.dbo.BINARYTABLE WHERE SEQ=1" QUERYOUT "C:\TEMP\Sample.jpg" -Slocalhost -T -fC:\TEMP\fmt.fmt' EXEC xp_cmdshell @cmd, NO_OUTPUT ただし、xp_cmdshellを利用するには、オプションの変更が必要。 sp_configure 'xp_cmdshell',1 GO reconfigure GO
2010.05.29
コメント(0)
-
Oracleの機能をSQL Serverで実装する～DMLトリガ
OracleのDMLトリガにあって、SQL ServerのDMLトリガにはないものというのは、主に以下の２つである。・BEFOREトリガ・行トリガでは、これらをSQL Serverで実装するにはどうするかについて書いてみたい。まず最初に書いておくべきOracleとSQL Serverの根本的な違いは、SQL Serverはデフォルトが自動コミットモードであって、トランザクションにするには明示的なトランザクションの開始が必要であるということだ。ただし、トリガの場合は元の更新のトランザクションの一部として作用する点はOracleと同じで、暗黙的なトランザクションと呼ばれることもある。そもそもBEFOREトリガや行トリガはどのような時につかわれるのだろうか？ORACLEの黒本には、以下の例が載っている。・平日の9:00～18:00以外は更新できないというセキュリティチェック・更新前に上限チェックを掛けて値を差し替えるトリガSQL ServerのDMLトリガに関しては、・トリガ内にトランザクション制御文を記述できる（Oracleは自律型トランザクションのみ）・INSTEAD OFトリガがテーブルにも使える（OracleはViewのみ）という違いがあり、これを活用することになる。1)平日の9:00～18:00以外は更新できないというセキュリティチェックこの場合は「トリガ内でロールバックする」という方法を利用するCREATE TRIGGER emp_securityON empAFTER INSERT,UPDATE,DELETEASBEGIN SET NOCOUNT ON IF (DATEPART(dw,GETDATE()) IN (1,7) OR RIGHT(CONVERT(varchar,GETDATE(),8),8) NOT BETWEEN '09:00:00' AND '18:00:00') BEGIN ROLLBACK TRAN RAISERROR ('時間外です',16,1) ENDENDこれを実行すると以下のようなメッセージとなる。「メッセージ 50000、レベル 16、状態 1、プロシージャ emp_security、行 11 時間外です メッセージ 3609、レベル 16、状態 1、行 1 トランザクションはトリガで終了しました。バッチは中止されました。」注意すべきは、SQL ServerのROLLBACK TRANはネストしていようがいまいが、その時点のすべてのトランザクションをロールバックしてしまうことだ。しかし、このようなセキュリティチェックは通常一連のトランザクションを全てロールバックを行って問題ないと考えられる。それでもトリガの入口と出口でトランザクション数を変えてしまうことは確かなので、明示的なトランザクションモードでロールバックを行う場合は、IF (@@TRANCOUNT>0) ROLLBACK TRANとするようにした方がよい。なお、ROLLBACKを行うケースで、しかし「更新しようとしたログ」は記録したい場合、テーブル変数を使う。ROLLBACKすると、DDLですらROLLBACKしてしまうSQL Serverだが、テーブル変数の中身はROLLBACKされないからだ。Oracleの自律型トランザクションの代わりとして利用できることがある。CREATE TRIGGER emp_securityON empAFTER INSERT,UPDATE,DELETEASBEGIN SET NOCOUNT ON DECLARE @data TABLE ([MODE][char](1),[EMPNO][int]) INSERT INTO @data SELECT 'I',empno FROM inserted UNION ALL SELECT 'D',empno FROM inserted IF (DATEPART(dw,GETDATE()) IN (1,7) OR RIGHT(CONVERT(varchar,GETDATE(),8),8) NOT BETWEEN '09:00:00' AND '18:00:00') BEGIN ROLLBACK TRAN RAISERROR ('時間外です',16,1) END INSERT INTO logtable SELECT MODE,EMPNO,GETDATE() FROM @dataENDちなみにどうしてもROLLBACKをしたくない場合、INSTEAD OFトリガを利用することになる。CREATE TRIGGER emp_securityON empINSTEAD OF INSERT,UPDATE,DELETEASBEGIN SET NOCOUNT ON IF (DATEPART(dw,GETDATE()) IN (1,7) OR RIGHT(CONVERT(varchar,GETDATE(),8),8) NOT BETWEEN '09:00:00' AND '18:00:00') BEGIN RAISERROR ('時間外です',16,1) RETURN END DECLARE @ins int DECLARE @del int SET @ins = (SELECT COUNT(*) FROM (SELECT TOP 1 * FROM inserted) i) SET @del = (SELECT COUNT(*) FROM (SELECT TOP 1 * FROM deleted) d) IF (@ins=1 AND @del=1) UPDATE emp SET ENAME=i.ENAME,SAL=i.SAL,DEPTNO=i.DEPTNO FROM emp e INNER JOIN inserted i ON i.EMPNO=e.EMPNO ELSE IF (@ins=1) INSERT INTO emp SELECT * FROM inserted ELSE DELETE e FROM emp e INNER JOIN deleted d ON d.EMPNO=e.EMPNOEND2)更新前に上限チェックを掛けて値を差し替えるトリガ inserted/deletedという論理テーブルには更新対象となった全てのデータが含まれているので、AFTERトリガで上限チェックを掛けた状態での更新をかぶせることになる。CREATE TRIGGER emp_salON empAFTER INSERT,UPDATEASBEGIN SET NOCOUNT ON IF UPDATE(sal) UPDATE emp SET sal= CASE WHEN d.sal*1.5<i.sal THEN d.sal*1.5 WHEN i.sal>5000 THEN 5000 ELSE i.sal END FROM emp e INNER JOIN inserted i ON i.EMPNO=e.EMPNO LEFT OUTER JOIN deleted d ON d.EMPNO=e.EMPNOEND とにかく１行つき１回何かの処理を行わないといけないとしたら、カーソルを利用する。CREATE TRIGGER emp_salON empAFTER INSERT,UPDATEASBEGIN SET NOCOUNT ON IF UPDATE(sal) BEGIN DECLARE @empno int DECLARE @emp_cur CURSOR SET @emp_cur = CURSOR FAST_FORWARD FOR SELECT empno FROM inserted OPEN @emp_cur FETCH NEXT FROM @emp_cur INTO @empno WHILE (@@FETCH_STATUS=0) BEGIN EXEC msdb.dbo.sp_send_dbmail .... FETCH NEXT FROM @emp_cur INTO @empno END ENDEND
2010.05.23
コメント(0)
-
ようやく全部2008に
今日70-451(Database Developer 2008)を受けてきた。しかし、195分で85問というのは多いですね。何かこういう試験あったなーと思ったら、Oracle11g Goldの新機能が85問でしたね。各問題は長くはないのですが、要件記述が曖昧なのが多くて、すごい頭を使いましたよ。結果は合格ですが、787点は微妙な感じ。やっぱり仕事で2005だと、FileStreamとか使わない機能は忘れていっちゃいますね。。ともかく、MCITPは全部2008トラックになったのでよかったことにしましょう。（試験が始まらないので結局予想よりも１年遅くなっちゃいましたが）
2010.05.15
コメント(0)
-
70-433合格
70-433(MCTS:SQL Server 2008 Database Development)を受けてきました。まあ、MCTSだし、１年前には新機能はみっちり試したし、毎日仕事で使ってるし。。と思って、何も準備しないで行ったんですけど、冷汗かきました。XMLもフルテキストインデックスもここまで詳しく訊かれるとは思ってなかったので。。それでも1時間近く余して見直しも完了し、終了ボタンを押したら、通常10数秒で返ってくる結果が30秒以上戻ってこなかった。かなりドキドキしましたね。結果は910点。合格。来週もう一つ70-451を受けなきゃ。。プロメトリックは９カ月ぶりだったけど、本人確認が厳しくなっている印象でしたね。前は身分証明書は受付時だけで試験場に持ち込む必要まではなかったけど。。替え玉受験とかあったのでしょうかね。
2010.05.09
コメント(0)
-
PL/SQLとTransact-SQL
SQL ServerのTransact-SQLと、OracleのPL/SQLを比較すると、２つの言語のコンセプトの違いを感じることが多い。PL/SQLではストアドプロシージャ内でSELECT文を書いても、結果をクライアントに返すことができない。これはストアドプロシージャは「処理（主に更新処理）」を行うものであって、結果が欲しいならばテーブルやビューに対してSELECTすべきだという基本理念があるのだろうと私は理解している。・結果を取りだすためにはカーソルが必要になる（カーソルの構文とバリエーションがPL/SQLは非常に多い）・複雑なクエリでもレスポンスを稼ぐための「マテリアライズドビュー」が用意される（SQL Serverで実装されている「インデックス付ビュー」よりも遥かに詳細なオプションを有している）・複雑なレポートを出力したい場合には２段階処理で中間実テーブルを介する必要がでてくる 1)ストアドを実行して結果をテーブルに入れる 2)そのテーブルをSELECTしてレポートに落とし込む見方を変えると、SQL Serverではストアドプロシージャやテーブル関数で結果を返すことができるから、上記いずれも力を入れてソリューションを用意する必要がないということだと思う。Transact-SQLが「SQL文を連続して実行させるデータベース機能」だとすれば、PL/SQLは「データベース側に記述する（所謂）プログラム」ということだ。結果としてTransact-SQLは対象データを一括して処理するセットベース処理に向き、PL/SQLは一行ずつ処理するカーソルベース処理に向く（セットベース処理には向かない）ということになる。別の例としては、SQL Serverのテーブル関数・テーブル変数があげられる。これに相当するPL/SQLの機能はコレクションということになるのだが、前者は明らかにテーブルであるが、後者は配列である。TABLE化してSELECTしたり、バルクバインドで値を入れることはできても、SQL文で加工することはできない。どうしてもカーソルが必要になる。面白いのは、このコレクション（ネストしたテーブルやVARRAY）が、テーブルの列の型として指定できることだ。この機能はリレーショナルデータベースの理論からは逸脱している。出来上がったテーブルが典型的な非正規形だからだ。しかし、「なぜこのような機能が必要なのか」には疑問を感じる。このような列に対して外部制約を付けることはできないし、トリガなどを考えると処理を複雑にするリスクすらある。つまるところ、「２つのテーブルよりも１つのテーブルにまとめた方が処理がしやすい」という発想で、それは結局「カーソルのダブルループを避ける」ということなのではないだろうか。同じことをSQL Serverで同じように行うことはできないが、もし求められたら別の方法で実現することになるだろう。例えばネストしたテーブル型を含むテーブルについてのクエリSELECT o1.orderId, o1.custId, o1.orderDate, o2.prodId, o2.price, o2.quantityFROM orders2 o1, TABLE(o1.items) o2;これは以下のように書くことになる。SELECT o1.orderId, o1.custId, o1.orderDate,o2.c.value('prodId[1]','varchar(10)') prodId,o2.c.value('price[1]','numeric(7,2)') price,o2.c.value('quantity[1]','numeric(4,0)') quantityFROM Orders2 o1CROSS APPLY items.nodes('/item') AS o2(c)そう、XMLを使うことになる。もともとSQL Serverではこのような目的でXML型の使用を推奨することはなく、今回のケースだと「２つのテーブルで管理する」ことが推奨されるはずだ。ただ、「テーブル内テーブル」の概念を実装する場合、SQL Serverではテーブル型ではなく、XMLを活用するというのがTransact-SQLとPL/SQLのアプローチの違いを示しているように思う。
2010.05.05
コメント(0)
-
PL/SQL Gold準備開始
去年の８月にPL/SQL Gold資格がリリースされたが、最近ようやく参考書が発売された。ここのところ仕事はSQL Server一辺倒で、PL/SQLも使わなければ忘れてしまいそうだが、こういう機会を捕まえて思い出しておくのもよいだろうと思う。調べてみると、予定通りOracle 11gR2はWindows 7にも対応しているようだ。しかし。。ダウンロードファイルがついに２つに分かれてしまった。サイズは合計2.1GB。Tianna!もう次はダウンロードでは効かないのではないだろうか。
2010.05.05
コメント(0)
-
MCITP-Database Developer 2008
さすがに丸１年以上経つと「本当にこの試験は始まるのか？」と疑いたくなる。通常の試験はとうに開始し、アップグレード試験も英語版ならば１年前には開始されている。では、なぜ日本語版のアップグレード試験は開始しないのだろうか。悩んでも仕方ないので、アップグレードはあきらめて、通常試験で挑むこととする。この方法だと２試験合格が必要なので、試験代が２倍かかるが、やむを得ない。こうなったら、アップグレード試験は当分開始しないで欲しいですね。
2010.05.05
コメント(0)
-
武蔵小杉の試験会場
OracleがプロメトリックからピアソンVUEに変更になり、プロメトリック系の試験会場はずいぶん厳しいことになるだろうなぁ、と思っていた。いつもお世話になっている武蔵小杉の試験会場でも前に雑談したときに、そのようなことを言っていた。しかし、最近見てみると、なんとピアソンVUEの試験会場と２本立てで運営することに成功しているようだ。（ピアソンVUEは非公開会場）http://www.breakthru.co.jp/test/ここは会場を別フロアで２つ持っているので、このようなことが可能なのかもしれない。いずれにしても、受験者にしてみれば、同じ場所で引き続き両方受験できるというのは大変ありがたい。もともと武蔵小杉は落ち着いて受験させてくれる、配慮の行き届いたとてもいい会場なのです。こういうのが企業努力。。なんでしょうねぇ。
2010.02.28
コメント(0)
-
SQL Server 2008のCLR
意外と取り上げられることが少ないようなのだが、SQL Server 2008になって、CLRも改善したところがある。特に大きいのがCLR集計関数。・内部バッファ8000バイトの制限がなくなった。・複数のパラメータを受けることができるようになった。以前、CLRでXIRR(内部収益率）を算出する集計関数を作ったことがあったが、SQL Server 2005では、パラメータを１つしか受けられないため、専用のデータ型を作って以下のように実装する必要があった。SELECT KEY,dbo.XIRR(dbo.GetCashFlowPair(date,amount))FROM TABLE1GROUP BY KEYそれでもこの関数には致命的な問題があった。それが「内部バッファ8000バイト」。この制約があると、すべての結果を一旦ため込まないと算出できないタイプの関数では、件数に制約が生じてしまうのだ。バッファがあふれたらアウト、その行については、数字が算出できない。しかし、SQL Server2008では、「MaxByteSize:-1」を指定できるようになったので、メモリが許せば2GBまでは蓄積可能。また、パラメータも複数受けられるので、SELECT KEY,dbo.XIRR(date,amount)FROM TABLE1GROUP BY KEYという実装が可能。この改善は大きいと思う。それはExcelにある多くの財務関数・統計関数も知識さえあれば、CLR集計関数で実装し、一気に算出することができるということだから。
2010.02.23
コメント(0)
-
結局１年遅れか
MCITPのDatabase Developerのアップグレード試験は「2010年春頃」に変更されていた。もともとは今年の4月に開始する予定だったもので、これで実質予定よりも１年遅れってことになる。なんでまたこんなに遅れるのか理解できない。
2009.12.19
コメント(0)
-
Oracleの試験はピアソンへ
結局Oracleの試験はプロメトリックからピアソンVUEに変更になるらしい。てっきり自前で新会社でもつくるのかと思っていたので、非常に意外ではあった。もともとシスコがプロメトリックを切り、報復のようにマイクロソフトがピアソンを切り、それぞれ１会社でしか試験が受けられなくなっていたが、ここでオラクルがプロメトリックを切った背景には何があったのかは気になる。（試験場で聞いたところでは、プロメトリックとの契約は更新期限ではなかったようだから、おそらく契約打ち切りにはペナルティがかかったと思うのだが）私はピアソンで試験を受けたことがない上に、質のいいプロメトリックの試験会場で受け続けてきていたので、この変更は少し残念だ。
2009.09.05
コメント(0)
-
KiｌimanjaroはSQL Server 2008 R2に
SQL Server 2010になるかと思っていたKilimanjaroは、結局SQL Server 2008 R2として来年リリースするようだ。やはり気になるのは「何が変わるのか」だが、どうも今一つピンとこない。--最大256個の論理プロセッサをサポート--複数サーバー管理の効率化--地理データの可視化をサポートする Report Builder 3.0 の提供この辺りは今後の情報に期待することとしたい。
2009.09.05
コメント(0)
-
３回目でようやく
XMLマスター プロフェッショナル（データベース）。めげずに今週も受けてきました（先週も２回目受けに行って77％で撃沈）。２回目での問題のダブり方から見て、準備されている問題は多くはなさそうだと思っていたが、内容が少し変えてある問題がまた何問も出てきた。それでも必死に考えて答えを導き、２回目よりは手ごたえあるかな、という状態で終了ボタンを押した。。。。合格。それにしても83％。あれだけいろいろ調べて臨んだが83％ではどこを間違えたのか、ヨクワカンナイ。VBAエキスパートのプロフェッショナルと同じで、正直もう受けたくない試験でした。まあ、なんとか合格点に届いてよかった。。
2009.08.30
コメント(0)
-
Windows7
Windows 7製品版がMSDNで配賦開始されてから１週間。ようやく自宅のPCに入れてみた。起動はあまり早くなっていないが、使っていると確かに軽くなった印象はあるね。家で使っているソフトは限られるが、互換性の問題は今のところ出ていない。
2009.08.23
コメント(0)
-
Oracleがプロメトリックで受けられなくなるらしい
＞オラクル認定試験提供会社変更のお知らせ(2009.8.21)弊社は、2009年9月26日にオラクル認定試験の配信提供会社を変更します。これにともない、プロメトリック認定会場での試験提供は2009年9月25日をもって終了になります。また、プロメトリック社での弊社試験予約最終日は2009年9月13日になりますのでご注意ください。＞2009年9月26日以降の弊社試験受験申込み方法などの詳細につきましては、9月第1週に弊社Webサイトおよび弊社メールマガジンにて告知致します。9月26日以降の受験のご検討もしくは受験申込みをされているお客様には、ご不便をおかけして大変恐縮でございますが、それまでお待ちいただくか、9月25日までにプロメトリック社で受験いただけますようお願いいたします。見る限りプロメトリックからピアソンVUEに変更、というわけでもなさそうだ。先日のライセンスポリシーの変更といい、殿様商売の傾向が強まっているから、自前で試験も実施するとか言い出すんじゃないだろうか。
2009.08.21
コメント(0)
-
XMLマスタープロフェショナル(データベース)
最近のデータベースを扱う以上は、XMLDBでなくてもXMLが扱えないとだめだろうと思い、以前から「RDBでXMLを使いこなす」ようになりたいと思っていた。で、XMLマスターの勉強をここのところしていたわけだが、XMLマスターベーシック(これは問題なく合格）に比べると、XMLマスタープロフェッショナル(データベース)はずいぶん難しかった。今日受けに行って玉砕。かなりXPath, XQueryをSQL Serverで打ち込んで臨んだのだが、あいまいに理解している部分をことごとく突かれてしまった感じ。30問で80％以上合格ということはMaxで6問しか間違えられないのに、問題を見て「？」だったものがすでに6問。これでは受かるはずがない。それにしても、わからなかった問題のうち、参考書・問題集では解答を知ることすらできないインデックス作成指針、スキーマ変更については、どうやって確認したらいいのだろうか。
2009.08.16
コメント(0)
-
いつまでたっても
もともとMCITPのDatabase Developer(Upgrade)は2009/4開始予定だったのに、2009/8に延期になっていた。そろそろかな、と思っていたら、今度は2009/11に延期だそうだ。いったい、いつになったら受けられるのでしょうかね。
2009.07.16
コメント(0)
-
PL/SQL Gold
PL/SQL Developer(Gold)の試験が８月に開始するらしい。http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=70⟨=JA&p_exam_id=1Z0_146以前はPL/SQLはミドルウェアトラックで、上にはOracle Forms Developer(Gold)があったのだけれど、いつのまにかPL/SQLはデータベーストラックに移動しており、おそらく上位資格がなくなったので、Gold資格を新設するということだろうと思う。しかし、ここまで来ると業務で本格的に書いてないときついな。> 外部Cプログラムのコール > 外部Javaプログラムのコール > Fine-Grained Acess Controlの実装とテスト> SecureFile LOBの重複除外、圧縮および暗号化の設定> SQLインジェクションに関する欠陥テストポイントを絞りきれない状態でOTNのマニュアルを洗いざらい読むのもかなりきつい（DBA11gで懲りた）ので、参考書の登場を待つとしますか。。
2009.07.14
コメント(0)
-
無償プレゼント
SQL Server 2008 スタートダッシュキャンペーン「TechNet Plus Direct サブスクリプション」無償プレゼントというものに申し込んだら、当選したらしい。「抽選で20名」なのに、よくあたったなぁ。。こういうのは当たった試しがないので、ちょっとうれしい。。
2009.06.05
コメント(0)
-
金額按分処理
SQL ServerはOracleに比べて分析関数の対応が少なく、実際にはROW_NUMBER()以外は用いることがほとんどない。それでも集計関数にもOVERが使えることを知っていると、ずいぶん楽になる。具体的には、金額配賦の計算があげられる。まとまったグループに対してきまった金額をその構成要素に割り振る計算で、実務上は避けて通れない。売上高按分だったり、面積按分だったり、ケースはさまざまだが、悩ましいのは金額だけに端数処理が必要になることだ。端数が出た場合は、一番配賦額の多いところで調整するのが一般的だからだ。(部署)支社 部署 売上高---------------------------東京 営業1部 100,123,520東京 営業2部 203,001,225東京 営業3部 92,254,210東京 営業4部 45,862,122東京 営業5部 145,225,656大阪 営業1部 85,255,456大阪 営業2部 54,225,781大阪 営業3部 23,248,892(費用)-----------------------------東京 人件費 250,000,000東京 賃料 23,500,000東京 光熱費 5,862,000大阪 人件費 130,000,000大阪 賃料 14,540,000大阪 光熱費 3,255,000発生した費用を各部署に売上高で按分するとした場合、SQL Server 2000では結構厄介だった。売上が同じだと二重に調整されてしまうから、ルールを決めて１部署だけを抜き出す必要があり、そのための連番を振る方法がSQL Server 2000ではIDENTITYしかなかった。○SQL Server 2000の場合DECLARE @配賦 TABLE([SEQ][int] IDENTITY,[支社][varchar](10),[費用][varchar](10), [金額][numeric](19,0),[部署][varchar](10),[配賦額][numeric](19,0))INSERT INTO @配賦(支社,費用,金額,部署,配賦額)SELECT b.支社,e.費用,e.金額,b.部署,ROUND(e.金額*b.売上高/t.支社売上高,0)FROM 部署 bINNER JOIN 費用 e ON e.支社=b.支社INNER JOIN (SELECT 支社,SUM(売上高) 支社売上高 FROM 部署 GROUP BY 支社) t ON t.支社=b.支社ORDER BY 1,2,5 DESCUPDATE @配賦SET 配賦額=配賦額-d.差額FROM @配賦 hINNER JOIN(SELECT 支社,費用,SUM(配賦額)-金額 差額 FROM @配賦 GROUP BY 支社,費用,金額) d ON d.支社=h.支社 AND d.費用=h.費用WHERE h.SEQ IN (SELECT MIN(SEQ) FROM @配賦 WHERE 支社=h.支社 AND 費用=h.費用) SELECT * FROM @配賦SQL Server 2005になって、ROW_NUMBERが使えるようになり、WITH句もサポートされたことで、テーブル変数がなくてもコンパクトに書けるようになった。○SQL Server 2005の場合SELECTROW_NUMBER() OVER (PARTITION BY b.支社,e.費用 ORDER BY b.売上高 DESC) SEQ,b.支社,e.費用,e.金額,b.部署,ROUND(e.金額*b.売上高/t.支社売上高,0) 配賦額FROM 部署 bINNER JOIN 費用 e ON e.支社=b.支社INNER JOIN (SELECT 支社,SUM(売上高) 支社売上高 FROM 部署 GROUP BY 支社) t ON t.支社=b.支社)SELECTh.支社,h.費用,h.金額,h.部署,h.配賦額-(CASE WHEN h.SEQ=1 THEN 差額 ELSE 0 END)FROM 配賦 hINNER JOIN(SELECT 支社,費用,SUM(配賦額)-金額 差額 FROM 配賦 GROUP BY 支社,費用,金額) d ON d.支社=h.支社 AND d.費用=h.費用これが集計関数のOVER句を利用すると純粋に１クエリで解決する。○SQL Server 2005の場合２SELECT支社,費用,金額,部署,配賦額-CASE WHEN SEQ=1 THEN SUM(配賦額) OVER (PARTITION BY 支社,費用)-金額 ELSE 0 ENDFROM(SELECTROW_NUMBER() OVER (PARTITION BY b.支社,e.費用 ORDER BY b.売上高 DESC) SEQ,b.支社,e.費用,e.金額,b.部署,ROUND(e.金額*b.売上高/SUM(b.売上高) OVER (PARTITION BY b.支社,e.費用),0) 配賦額FROM 部署 bINNER JOIN 費用 e ON e.支社=b.支社) tmp
2009.05.26
コメント(0)
-
ストアドプロシージャのパラメータをXMLで
「インサイド SQL Server 2005」シリーズは４冊ある。・ストレージエンジン編・クエリチューニング＆最適化編・T-SQLクエリ編・T-SQLプログラミング編しかし、邦訳が出版されたのは上の２冊だけで、あとの２冊は結局邦訳されなかった。そこで「T-SQLプログラミング編」を原書で購入して読んでいた。やはりすごい本だ。この中で、「ストアドプロシージャのパラメータをXMLで受け渡すこと」について言及されていてハッとした。SQL Server2008ではテーブル値パラメータがサポートされたが、それまではストアドプロシージャにはスカラー形式でのパラメータしか渡すことができなかった。実際にフロントエンドにAccessや.NETのグリッドなどを使っていると、複数セットのデータをパラメータとして受け渡したいことがよく出てくる。たとえば、CustomerID,TradeID,Date,ProdCD,Amountを複数セット渡して更新を掛けるケースを考える。ワンセットであれば、以下のようなパラメータでもいい。EXEC sp_UpdateInfo @CustomerID,@TradeID,@Date,@ProdCD,@Amountしかし、パラメータの数が多かったり、複数セットの場合はバラバラのパラメータを用意していてはキリがないので、パラメータは１つにしてデリミタで区切って渡したりする。EXEC sp_UpdateInfo @PARMこの@PARMに「12345678,12,2008/12/31,101,25!222345678,12,2009/01/04,102,20」などという値をセットすることになるわけだ。一方、ストアドプロシージャ側でもこれを区切ってテーブルに戻すために、「デリミタ区切りの文字列をテーブル化する」ようなテーブル関数を予め準備しておく必要がある。仮にこれをsf_TOTABLEとすると、テーブル化するのには意外と手間がかかる。DECLARE D_CUR CURSOR LOCAL FOR SELECT ITEM FROM sf_TOTABLE(@PARM,'!') ORDER BY SEQOPEN D_CURFETCH NEXT FROM D_CUR INTO @ITEMWHILE (@@FETCH_STATUS=0)BEGIN INSERT INTO #TABLE SELECT MAX(CASE WHEN SEQ=1 THEN ITEM END) CustomerID, MAX(CASE WHEN SEQ=2 THEN ITEM END) TradeID, MAX(CASE WHEN SEQ=3 THEN ITEM END) Date, MAX(CASE WHEN SEQ=4 THEN ITEM END) ProdCD, MAX(CASE WHEN SEQ=5 THEN ITEM END) Amount FROM sf_TOTABLE(@ITEM,',') FETCH NEXT FROM D_CUR INTO @ITEMENDCLOSE D_CURDEALLOCATE D_CUR実際にこの方法で受け渡しを行ったりしているのだが、やはりスマートとはいい難い。二重のデリミタに加えて、「何カラム名が何の項目か」も分からない状態で受け渡しをすることになる。では、XMLをパラメータにするとどうなるか。パラメータの内容は以下の形になるだろう。＜Parameters＞＜CustomerID＞12345678＜/CustomerID＞＜TradeID＞12＜/TradeID＞＜Date＞2008/12/31＜/Date＞＜ProdCD＞101＜/ProdCD＞＜Amount＞25＜/Amount＞＜/Parameters＞＜Parameters＞＜CustomerID＞22345678＜/CustomerID＞＜TradeID＞13＜/TradeID＞＜Date＞2009/01/04＜/Date＞＜ProdCD＞102＜/ProdCD＞＜Amount＞20＜/Amount＞＜/Parameters＞EXEC sp_UpdateInfo @PARM受け取ったストアドプロシージャ側では@PARMという１つのパラメータを受け取るにも関わらず、それぞれの項目が何かも把握できる。そしてテーブル化にはテーブル関数もカーソルも必要がない。INSERT INTO #TABLESELECT x.c.value('(./CustomerID/text())[1]','varchar(max)') CustomerID,x.c.value('(./TradeID/text())[1]','varchar(max)') TradeID,x.c.value('(./Date/text())[1]','varchar(max)') Date,x.c.value('(./ProdCD/text())[1]','varchar(max)') ProdCD,x.c.value('(./Amount/text())[1]','varchar(max)') AmountFROM @XML.nodes('/Parameters') AS x(c)SQL Server 2005限定とは言え、これは悪くない方法だと思う。
2009.05.09
コメント(0)
-
Excel2007のシートコピー
Excel2007について。。Excelへのレポート等の出力でテンプレートを新しいブックにコピーする方法を使ったりすることがある。で、Excelのテンプレートをxlsxで作成し、新しいブックを作成してコピーするマクロを書く。Sub Test() Dim xlApp As Object Dim xlBook As Object Dim xlTemp As Object Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlTemp = xlApp.Workbooks.Open("C:\Temp\Temp.xltx") xlTemp.Worksheets("Template").Copy xlBook.Worksheets(1) xlTemp.Close False xlApp.Visible = True Set xlTemp = Nothing Set xlBook = Nothing Set xlApp = NothingEnd Sub上記の処理はExcel2007で実行してもエラーになることがある。「実行時エラー '1004': 移動先またはコピー先のブックの行列数が元のブックの行列数よりも少ないため、シートを移動先またはコピー先のブックに挿入できません。データを別のブックに移動またはコピーするには、データを選択して、[コピー]コマンドと[貼り付け]コマンドを使用して移動先またはコピー先のブックのシートの挿入してください」これはExcelのオプションでデフォルトの保存形式を「Excel2000-2003(xls)」に変更していると発生する。保存形式が変更されていると、新規ブックも自動的に互換モードで開くから、シートのサイズが合わず、エラーになるわけだ。Excel2007は世の中的にはまだそれほど多く使われていないため、安易に保存した形式がxlsxになっていると、外に送ったときに「開けない」と言われてしまう可能性が高いから、予防的にデフォルトの保存形式を変更しているのだから、これはダメだとは言えない。では、保存形式に関わらず、新規ブックを互換モードで開くためにはどうするか。いろいろ試行錯誤した結果としては、空のbook.xlsxというExcelファイルを作成しておき、以下のようにする Set xlBook = xlApp.Workbooks.Add("C:\Temp\Book.xlsx")これで新規ブックはBookという名前(重複していればBook1～)で2007モードで作成され、コピーは失敗しなくなる。
2009.05.09
コメント(0)
-
70-454は８月開始に延期
いきなり４か月も延期するとはいったい何を考えているのだ。。
2009.04.16
コメント(0)
-
SQL Server 2008 SP1
SQL Server 2008 SP1がリリースされた。http://www.microsoft.com/downloads/details.aspx?displaylang=ja&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19#filelistしかし、機能的にはめぼしい追加はないようだ。・スリップストリーム ベースとなるインストールにサービス パック (または修正プログラム) を統合し、それらを一度にインストールできるようになりました。 ・サービス パックのアンインストール サービス パックだけを (インスタンス全体を削除せずに) アンインストールできるようになりました。 ・レポート ビルダ 2.0 ClickOnce 機能それよりも気になったのは、「サポートされているオペレーティング システム : Windows Server 2003; Windows Server 2008; Windows Vista」。そう、XPが含まれてない。Enterprise以外はサポートしていたのだが、SP1からはサポートしなくなるのか・・？
2009.04.10
コメント(0)
-
Charter MemberとEarly Adopter
今回日本語版リリース直後にSQL Server 2008の資格を取得したので、「Charter Memberだろう」と少し期待をしていた。Charter Memberというのは、別に大したものではないが、試験開始から６か月以内にMCPの資格を取得するとCharter Member専用の認定証が送られてくるというものだ。過去に取得できたものとしては、SQL Server 2005のBusiness Intelligence Development(MCTS:40-445)だけ。今日認定証が４通(MCITP２通、MCTS２通)まとめて送られてきたので、期待して開けてみると、普通の認定証だった。MCPのサイトを見てみると、こんな記述があった。http://www.microsoft.com/learning/mcpexams/charter/default.mspxQ. What is the difference between a charter member and an early adopter? A. Charter members are the first group of individuals to achieve a new certification within six months of its release. Early adopters are those people who achieve a new version of an existing certification (for example, an MCITP: Database Administrator as revised for Microsoft SQL Server 2008) within six months of its release. Early adopters receive the standard certificate of achievement. なんだ。アップグレードだとCharter Memberにはなれないのか。。少しがっかりした。しかし一方で、海外のある会社のサイトで70-453合格者の認定証を掲示しており、そこにはちゃんと「CHARTER MEMBER」の文字があった。CHARTER MEMBERの認定証一体どういうルールになっているのだろう。謎だ。
2009.03.28
コメント(0)
-
すばらしい本
最近になってようやく以下の本を購入した。「インサイドMicrosoft SQL Server 2005 クエリチューニング&最適化編」読んでみてびっくりした。ここまでオプティマイザの動きを詳しく書いているとは。ストレージエンジン編も確かに興味深く読んだが、実際の運用では「SQL Serverはデータをどのように管理しているか」より、「SQL Serverはクエリをどのように実行するか」の方がはるかに直接的に役に立つのだ。この本を読むと、クエリプランのすべての表示の内容を理解できるようになる。いままでは、せいぜいグラフィカルプランを見て、「あーインデックスが使われていないな」とか「こちらのクエリの方が想定コストが低いな」程度のいってみればややいい加減な使い方で、むしろ「常識的なクエリの書き方（それこそインデックスが使われるには等）」に依存していたのは否めない。SQL Serverでももちろんその「常識」は通用するが、この本で得た知識でさらに一歩進んで分析できるようになる気がする。
2009.03.25
コメント(0)
-
CROSS APPLY/OUTER APPLY
CROSS APPLY/OUTER APPLYという結合演算子はSQL Server 2005で導入された。これはＡとＢを結合する際に、Ｂ自体の条件にＡの内容を含めるというもので、Ｂがテーブル関数のときに有効ということになっている。構造上パフォーマンスが良好とは言えないので、敢えて実業務で使うことはなかったが、実際には使った方がいい例もあることに気づいた。・ローン毎の償還予定(返済日,返済金額)を生成して返す関数（getschedule)があるときに、 すべてのローンの返済総額、最終期限と平均残存期間を取得したい。 すでに「償還予定」テーブルが存在していて、この関数がその内容を加工して返すだけのものであれば、そもそもその目的にテーブル関数を使う必要はない。 ただ、この関数が内部的に償還予定を生成して返すようなものであった場合、 CROSS APPLYを使わなければ処理に一時テーブルをカーソルが必要になる。DECLARE TABLE @SCHD ([ローン番号][int],[返済日][datetime],[返済金額][money])DECLARE @LOANID intDECLARE SCUR CURSOR LOCAL FOR SELECT ローン番号 FROM ローンマスタOPEN SCURFETCH NEXT FROM SCUR INTO @LOANIDWHILE (@@FETCH_STATUS=0)BEGIN INSERT INTO @SCHD SELECT @LOANID,返済日,返済金額 FROM getschedule(@LOANID) FETCH NEXT FROM SCUR INTO @LOANIDENDCLOSE SCURDEALLOCATE SCURSELECT ローン番号,SUM(返済金額),MAX(返済日), SUM(DATEDIFF(m,@BASEDATE,返済日)*返済金額)/SUM(返済金額)FROM @SCHDGROUP BY ローン番号・もしくは、SELECT内でサブクエリを切る方法もあるが、ロジックの重複はさけられない。SELECT l.ローン番号, (SELECT SUM(返済金額) 返済総額 FROM getschedule(l.ローン番号)), (SELECT MAX(返済日) 最終期限 FROM getschedule(l.ローン番号)), (SELECT SUM(DATEDIFF(m,@BASEDATE,返済日)*返済金額)/SUM(返済金額) 平均残存期間 FROM getschedule(l.ローン番号)) FROM ローンマスタ l・正直、ここまでやるくらいなら、CROSS APPLYを使った方がいいだろう。SELECT l.ローン番号,s.返済総額,s.最終期限,s.平均残存期間FROM ローンマスタ lCROSS APPLY (SELECT SUM(返済金額) 返済総額,MAX(返済日) 最終期限, SUM(DATEDIFF(m,@BASEDATE,返済日)*返済金額)/SUM(返済金額) 平均残存期間 FROM getschedule(l.ローン番号)) sまた、テーブル関数でなくても、以下の場合も有効と思われる。・各支社の年長者上位３名の氏名と生年月日を表示する。 支社マスタ(支社コード,支社名) 社員マスタ(社員コード,氏名,支社コード,生年月日) OUTER APPLYであれば、こう書ける。SELECT s.支社コード, s.支社名, m.氏名, m.生年月日FROM 支社マスタ sOUTER APPLY (SELECT TOP 3 氏名,生年月日 FROM 社員マスタ WHERE 支社コード=s.支社コード ORDER BY 生年月日) m しかし、OUTER APPLYを使わない場合は結構厄介である。生年月日はユニークキーではないので、同一生年月日が何人いても必ず最大３名を出すためには、サブクエリを使うことができない。したがって、ROW_NUMBERを使うことになるが、以下の通り。SELECT s.支社コード, s.支社名, m.氏名, m.生年月日FROM 支社マスタ sLEFT OUTER JOIN (SELECT * FROM (SELECT 在籍コード,氏名,生年月日, ROW_NUMBER() OVER (PARTITION BY 支社コード ORDER BY 生年月日) SEQ FROM 社員マスタ) m1 WHERE SEQ
2009.03.20
コメント(0)
-
SSRS PDFフォント埋め込み
どこかのページで言及されているのに気付きました。SQL Server 2005 SP3でPDFフォント埋め込みが対応されたらしい。（SQL Server 2008も対応済とか）http://technet.microsoft.com/ja-jp/library/dd353312(SQL.90).aspx試してみると、確かにSP3をあてたものは正しくPDFが作成された。これでXP/Vista関係なく日本語フォントが使えるようになりますねぇ。
2009.03.16
コメント(0)
-
アップグレード試験で認定されるもの
MCITPのアップグレード試験に合格すると、その下のMCTS資格も同時に認定してくれるらしい。SQL Server 2005 の MCITP からのアップグレードパスで、今日いきなりMicrosoftからメールが４通来た。Sub:Congratulations on Your Microsoft Certification! Access Your BenefitsCongratulations on earning your Microsoft Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance certification! We hope you enjoy the benefits of your certification and of membership in the Microsoft Certified Professional community.DBAとBIDの２資格＋それぞれのMCTSで４通。ということは、これ↓申し込めるんだな！さっそく申し込んでみた。「好評開催中の 「SQL Server 2008 スタートダッシュキャンペーン」に新たなチャンスがプラス！ 2009 年 5 月15 日までに SQL Server 2008 対応の MCTS 資格を取得し、キャンペーンに応募いただいた方から抽選で 20 名様に TechNet Plus Direct サブスクリプションを 無償で提供いたします！」
2009.03.15
コメント(0)
-
アップグレード成功
70-453 : UPGRADE MCITP SQL Server 2005 DBA から MCITP SQL Server 2008への移行 TS：Microsoft SQL Server 2008、実装とメンテナンス：863 PRO：Microsoft SQL Server 2008、データベース管理ソリューションの設計、最適化、および保守：833 結果：合格70-455 : UPGRADE MCITP SQL Server 2005 BI デベロッパー から MCITP SQL Server 2008 BI デベロッパー TS：Microsoft SQL Server 2008, Business Inteligence Development and Maintenance：792 PRO：Microsoft SQL Server 2008, Designing a Business Inteligence Solution：775 結果：合格さすがに２連チャンは疲れました。驚いたのは、２つとも試験の途中でいきなり再起動されて、ログイン画面に戻ったこと。こんなの前にはなかったけど、試験センターの説明には「まれに」というコメント付きでそのことが書いてあった。もう一回ログインすると、続きからできるようになっていたけど。あと、70-455は明らかに正解が２つあるもの（２つの選択肢が１字１句同じ）が２つもあった。そのうち改善するのだろうか。セカンドショットキャンペーンで臨んだが、ともかく一発合格でよかった。
2009.03.14
コメント(0)
-
ついに開始
ついにMCITPのSQL Server 2008トラックの試験がリリースされたようだ。まずは70-453 UPGRADE: Transition your MCITP Database Administrator Skills to MCITP Database Administrator 2008 70-455 UPGRADE: Transition your MCITP Business Intelligence Developer Skills to MCITP Business Intelligence Developer 2008 の２つで、Database Developerは４月下旬だそうだ。とりあえず最初はガチンコでいくしかない、ということで、さっそく申し込んでみた。どんな内容かわからないので、セカンドショットキャンペーン中だし、それで行こうと。しかし、今までバウチャーを買ったことがないので、何だかいろいろ手間だなと思っていると、いつも受験している試験会場の申込サイトを見てみると、「現在の期間は自動的にセカンドショットキャンペーンが適用されます」「セカンドショットキャンペーンチケット番号発行お申し込みページで番号を取得いただく必要はありません」と書かれていた。こりゃー便利だ。要はただ申し込んだだけで適用になるらしい。やっぱりここで直接申し込もうと思ったが、よく見たらまだ試験がリストになかった。メールでお願いしたら、すぐに足してくれた。そしたらセカンドショット用のバウチャー番号がすぐに送られてきた。SQL Server 2008はOracle11gとは違って、どう考えても新機能だけで試験が作れるとは思えない。そういう意味ではSQL Server 2005でも日頃使わない機能は再確認しておく必要ありですな。。
2009.03.04
コメント(0)
-
Notification Service廃止はどこに書いてあるか
SQL Server 2005では、コアなサービスとして以下のものが存在していた。・データベースエンジン・Analysis Services(SSAS)・Integration Services(SSIS)・Reporting Services(SSRS)・Notification Services(SSNS)・レプリケーション、Service Broker、フルテキスト検索このうち、SQL Server 2008でなくなってしまったものがある。・Notification Services(SSNS)しかし、このサービスはどこに行ってしまったのか、代替は何なのか、漠然と調べていてもわからない。実際には、以下の場所に記載がある。○SQL Server Reporting Serviceで廃止された機能http://msdn.microsoft.com/ja-jp/library/ms144231.aspx「Notification Services は、SQL Server 2008 から削除されました。データ ドリブン サブスクリプションなど、Reporting Services の既存の機能を使用して、以前のバージョンの SQL Server の Notification Services で提供されていた機能の一部を利用できます。」もともとNotification Serviceというのは、SQL Server 2000の追加コンポーネントとしてリリースされたもので、「株価がx円になったら通知を受け取りたい」というようなニーズを想定してのものだった。一方、SSRSのデータドリブンサブスクリプションというのは、データの内容に応じてメール送信先やレポート内容を変更するという機能であって、データの内容に応じてプロセスを起動をコントロールするというものではない（イベントドリブンサブスクリプションではなく、起動タイミングはタイマーかスナップショット生成によってのみコントロールされる）。したがって、Notification Serviceの本来の機能の代替になるとは思えない。結局イベントの検知は、サービスブローカーやデータ収集、もしくは従来通りのトリガ生成などを利用して作るしかなく、機動的なメール送信にはデータベースメールを利用するしかないのではないかと思う。ここまであっさりとサービスを１つ廃止してしまったのは、それだけ利用者が少なかったということだろうか。（まあ、設定も簡単ではなかったが）
2009.02.22
コメント(0)
-
複数テーブルによるUPDATE
Oracleで、複数テーブルによる更新を行う場合、通常以下のように書くことが多い。UPDATE TBL2 aSET (F1,F2)=(SELECT F1,F2 FROM TBL1 WHERE KEY=a.KEY);ただ、このサブクエリによる更新は件数が多いと時間がかかる。その場合、クエリによる更新が使える。UPDATE (SELECT a.F1 AF1,a.F2 AF2, b.F1 BF1, b.F2 BF2FROM TBL2 aINNER JOIN TBL1 b ON b.KEY=a.KEY)SET AF1=BF1,AF2=BF2;ただし、この書式は結構制約が多い。以下の書き方はできない。UPDATE (SELECT a.F1 AF1,a.F2 AF2, b.F1 BF1, b.F2 BF2FROM TBL2 aINNER JOIN TBL1 b ON b.KEY=a.KEY)SET (AF1,AF2)=(BF1,BF2);ORA-01767: UPDATE ... SET式は副問合せである必要がありますまた、対象テーブルに主キーが設定されていないと、ORA-01779: キー保存されていない表にマップする列は変更できません上記は主キーを設定すればうまくいくが、ROWNUM疑似列を使って更新するケースでは、UPDATE (SELECT a.F1,b.SEQFROM TBL2 aINNER JOIN (SELECT KEY,ROWNUM SEQ FROM (SELECT KEY FROM TBL1 ORDER BY KEY DESC)) b ON b.KEY=a.KEY) SET F1=SEQ;ORA-01779: キー保存されていない表にマップする列は変更できませんこれはROW_NUMBERを使っても同じ。UPDATE (SELECT a.F1,b.SEQFROM TBL2 aINNER JOIN (SELECT KEY,ROW_NUMBER() OVER (ORDER BY KEY DESC) SEQ FROM TBL1) b ON b.KEY=a.KEY) SET F1=SEQ;ORA-01779: キー保存されていない表にマップする列は変更できませんこの場合は、時間がかかっても以下の方法しかない。UPDATE TBL2 aSET F1=(SELECT SEQ FROM (SELECT KEY,ROWNUM SEQ FROM (SELECT KEY FROM TBL1 ORDER BY KEY DESC)) WHERE KEY=a.KEY)
2009.02.11
コメント(2)
-
SSISで一時テーブルを使う
SSISで一時テーブルを使って処理を行いたいときのポイント「SQL実行タスク」を実行してワークテーブルを作成し、「データフロータスク」でそのワークテーブルを使うケースを考えてみる。1)接続を維持する・SQL ServerのOLEDB接続マネージャがデフォルトのままだと、通常は接続はタスク開始時に確立され、終了時に切れる。したがって、「SQL実行タスク」が完了すると接続が切れる。・ローカル一時テーブルはセッション単位に有効であるため、作成してもタスク終了時点でなくなってしまう。グローバル一時テーブルはセッションをまたがって参照できるが、参照しているセッションがゼロになった瞬間になくなってしまうため、やはりタスク終了時点でなくなってしまう。・したがって、SQL ServerのOLEDB接続マネージャのRetainSameConnection=Trueに変更する。これにより一度確立したセッションはパッケージ終了まで維持される。2)データフロータスクで一時テーブルを使う・データフロータスクの変換先としてテーブルを使うには、設定時にテーブルが存在していないと選べないため、通常はSSMS(SQL Server Management Studio)などを使って、グローバル一時テーブルを作成し、変換先のリストに登場させる。（設定完了後は、SSMSの接続を切るなどして、グローバル一時テーブルはDROPしてしまうが、それ以降は「OLEDB変換先」はエラーマークが表示された状態になる）・このまま実行すると、実行前の検証で「変換先のテーブルが存在しない」というエラーが発生する。実際にタスクを実行する瞬間には、一時テーブルが存在することになるため、実行前タスクの内容を検証しないようにする。具体的には「データフロータスク」のDelayValidation=Falseに変更する3)データフロータスクでローカル一時テーブルを使う・ローカル一時テーブルも使うことはできる。しかし、2)の方法では同じセッション一時テーブルを作成することはできないため、2)の設定のあと「OLEDB変換先」のプロパティ(OpenRowSet)を書きかえる。4)一時テーブル作成は並行実行させない・上記フローを並列して複数実行するように設定すると、うまくいかないことがある。たとえば、一時テーブルを作成する「SQL実行タスク」と、その一時テーブルにデータを取り込む後続の「データフロータスク」をワンセットとして、３つ並べてシーケンスコンテナに入れて実行した場合、どれか１つがエラーになる可能性が高い。・エラーになるとき、SQLプロファイラで監視してみると、以下のような処理が行われるEvent Class TextData SPIDAudit Login -- network protocol: TCP/IP 53SQL BatchCompleted CREATE TABLE #TEMP2 (F1 char(2),F2 int) 53Audit Login -- network protocol: TCP/IP 54SQL BatchCompleted CREATE TABLE #TEMP3 (F1 char(2),F2 int) 53SQL BatchCompleted CREATE TABLE #TEMP1 (F1 char(2),F2 int) 54Audit Logout 54（以後SPID=53にてデータ取り込み処理） ３つの一時テーブルを作成するときに、当初からあるセッション(53)に加えて、もう一つのセッション(54)が新たに起動し、#TEMP1を作成してから切れている。このような動きになる場合は、セッション(54)が切れた瞬間に#TEMP1はなくなり、#TEMP1に対する後続のデータフロータスクがエラーとなる。・並列実行の判断はSSISが行うため、上記を回避するためには、 a)一時テーブルを作成するSQL実行タスクは並列実行させない b)それぞれのフローに違うOLE接続マネージャを割り当てる c)パッケージのMaxConcurrentExecutablesを-1から1に変更する(-1はCPU数+2を示す既定値） d)すべての処理を直列に配置する
2009.01.12
コメント(0)
-
ドメインエラーが発生しました
ここ１年以上走っているレポートをやや特殊なデータ内容で生成してみたら、エラーが発生した。「ドメインエラーが発生しました」さすがにびっくりして、権限設定を確認したり、SQL Serverのエラーログをみたり、いろいろ調べてみたけど、最終的にわかったのは無効な演算に対するエラーメッセージだということだ。この計票ではPOWER(a,b)を使っていて、通常の場合発生しないが、今回のケースではaがマイナスになることがあった。bは小数だから結果が虚数になり、エラーになったというわけ。SQL Server2008ではメッセージは変更されていた。「無効な浮動小数点演算が実行されました」
2009.01.09
コメント(0)
-
レコードセット変換先を使う
SSISでファイルの内容を読み出すが、データベースには入れたくない場合、言い換えれば情報だけ抽出して使いたい場合には「データフロータスク」の「レコードセット変換先」は便利だ。ただ、読み出したものをどうやって使うかは意外と面倒くさいので、備忘録的に。1:For Eachループコンテナを使う Foreach ADO列挙子を使えば、１行ずつ読みだすことができるので、比較的容易。 ただし、読みだすためにコンテナと変数の定義が必要なので、処理の内容の割にはフロー上大掛かりなものになってしまう。2:スクリプトタスクで、ADOレコードセットとして使う レコードセット変換先はインラインADOレコードセットであるとなっているが、ADO.NETのDataAdapterなどでうまく変換できないので、結局レガシーのADOを使う方法が一般的。 この方法は以下の細工が必要。 ・ADODB.dllはSSISから参照可能ではないため、ADODB.dllをProgramFilesフォルダから検索して、Windows\Microsoft.NET\Framework\の該当バージョンのフォルダにコピーする ・以下にあるReadVariable関数を組み込む ただし、以下のように列数も簡単に取れるので、ADOになれた人には使いやすい。Public Sub Main() Dim rs As ADODB.Recordset rs = ReadVariable("FileData") Dts.Variables("ColCnt").Value = rs.Fields.Count Dts.TaskResult = ScriptResults.SuccessEnd Sub----------------------------------------------------------------------------Private Function ReadVariable(ByVal varName As String) As Object Dim result As Object Try Dim vars As Variables Dts.VariableDispenser.LockForRead(varName) Dts.VariableDispenser.GetVariables(vars) Try result = vars(varName).Value Catch ex As Exception Throw ex Finally vars.Unlock() End Try Catch ex As Exception Throw ex End Try Return resultEnd FunctionPrivate Sub WriteVariable(ByVal varName As String, ByVal varValue As Object) Try Dim vars As Variables Dts.VariableDispenser.LockForWrite(varName) Dts.VariableDispenser.GetVariables(vars) Try vars(varName).Value = varValue Catch ex As Exception Throw ex Finally vars.Unlock() End Try Catch ex As Exception Throw ex End TryEnd Sub
2009.01.04
コメント(0)
全148件 (148件中 1-50件目)
-
-
- 懸賞フリーク♪
- もうひとつ当選メール
- (2026-05-24 08:50:58)
-
-
-
- お買い物マラソンでほしい！買った！…
- 訳あり 賞味期限 間近SALE！トリプ…
- (2026-05-24 11:05:13)
-
-
-
- ★つ・ぶ・や・き★
- 庭で採れた “ 新じゃが ” 達に、しみ…
- (2026-05-24 11:50:03)
-
