לא פעם אנחנו נזקקים למעקב ותיעוד ריצות של פרוצדורות או קטעי קוד אחרים (סקריפטים, טריגרים, ג'ובים..), מסיבות שונות כגון:
1. בשלב הפיתוח – מעקב אחר התקדמות הריצה כדי לוודא שהקוד כתוב נכון ורץ כמתוכנן, ואם לא- הסיבות לתקלות. בשלב זה נרצה לרוב לקבל את הפלט למסך בו מתבצע הפיתוח.
2. בשלב הייצור – מידת השימוש בפרוצדורה, מעקב אחר הריצה (באיזה שלב היא), זמני ריצה של הפרוצדורה כולה ושל קטעים בעייתיים בה, סיבות לתקלות במקרה של נפילה. בשלב זה נרצה לרוב לקבל את הפלט לטבלה או לקובץ טקסט אותם ניתן יהיה לתחקר.
לרוב זה נעשה בצורה מאולתרת- אני מוצא עצמי בשלב הפיתוח מוסיף לקוד פקודות בסגנון Print 10, Print 20 וכו' כדי לדעת עד לאיזה שלב הריצה הגיעה עד כה, או באיזה שלב היא נפלה (ולעיתים אני משרשר להן ערכים של משתנים שאני רוצה לדעת מה היה ערכם באותה נקודה); ועם המעבר לייצור אני "מעיר" (Comment) את פקודות ה-Print כי אינן נחוצות עוד, אך משאיר אותן להפתעות לא צפויות בהמשך.
במקביל מקובל לא פעם ליצור טבלת Log במערכת כשכל פרוצדורה מעדכנת אותה עם תחילת הריצה ועם סיומה (סיום תקין בסוף או לא תקין בבלוק ה-Catch), וטבלה זו מאפשר לדעת מי הריץ מה ואיך, ומה הורץ מתי וכיצד.
כשנוצרת תקלה בייצור ופרוצדורה נכשלת- ניתן לעיין בקובץ הלוג שיצר הג'וב שהריץ אותה ולנסות לדלות את המידע הקיים בו, כל זה בהנחה שהוגדר קובץ כזה ושהוא לא נדרס כשניסינו להריץ שוב את המערכת (בדרך כלל זו "העזרה הראשונה" לפני שמזעיקים את הַמְּפתח שישבור את הראש..).
בקיצור- פתרונות שונים ומאולתרים, איש כיד הדמיון הטובה עליו, בשעה שראוי שתהיה מערכת שיטתית ואחידה לכל הצרכים.
כיצד תתנהל מערכת זאת? לא ערכתי מחקר בקרב המפתחים לגבי צרכיהם והרגלי עבודתם, ואינני מתחייב שהרגלי העבודה שלי הם הנכונים והיעילים ביותר או שהפתרון שלי הוא המוצלח מכולם, ולכן ניתן להתייחס לכתוב להלן כאל "הצעת הגשה", ומתוכו ניתן את התוך לאכול ואת הקליפה לזרוק..
קודם כל נדרשת טבלת מעקב: פרטים כמו שעה, שם משתמש, האפליקציה וכו' – יוגדרו כערכי ברירת מחדל; והשאר יוזנו במהלך הריצה:
Use tempdb;
Go
If Object_ID('T_Maakav') Is Not Null Drop Table T_Maakav;
Go
Create Table T_Maakav(Mone Int Identity,
TaarihMale Datetime Default GetDate(),
Taarih As Cast(DateDiff(Day,0,[TaarihMale]) As Datetime),
[DB_Name] Sysname Default DB_Name(),
[Object_Name] Sysname,
[User_Name] Sysname Default User_Name(),
[System_User] Sysname Default System_User,
[App_Name] Sysname Default App_Name(),
[Host_Name] Sysname Default Host_Name(),
SPID Int Default @@SPID,
Connection_ID UniqueIdentifier,
Transaction_ID Bigint,
Mispar VarChar(10),
Pratim Varchar(Max),
Pratim_XML XML);
Go
עשר העמודות הראשונות (Mone – SPID) די ברורות ולרובן ערכי ברירת מחדל אוטומטיים.
Connection_ID & Transaction_ID נלקחים מפונקציות ה-DMV הרלוונטיות כשהראשון מאפשר לזהות ריצות שלמות (בהן הופעלו פרוצדורות שונות), והשני טרנזקציות נפרדות – בעיקר לצורך טיפול במקרים בהם מתבצע RollBack לטרנזקציה, אך נרצה לשמור את העדכונים לטבלת המעקב.
Mispar הוא אותן פקודות Print 10, Print 20 וכו' שהזכרתי בהתחלה שמאפשרות לזהות בקלות את נקודות הדיווח השונות לאורך הפרוצדורה על ידי מיספורן.
Pratim כולל הודעה, לרוב דינאמית, לגבי השלב בו נמצאים, ערכי פרמטרים ומשתני מערכת משמעותיים, ועוד.
Pratim_XML – הרחבה של הקודם: המידע מוזן כ-XML וכך ניתן לשלוף אותו כעמודות נפרדות ולעיתים אף כשורות נפרדות (בהמשך אדגים כיצד לשמור לביקורת תוכן של טבלה, ולשלוף אותה מתוך ה-XML).
כעת נדרשת פרוצדורה מתאימה שתעדכן את טבלת המעקב או תדפיס את המידע למסך. ניתן יהיה להחזיק גרסאות שונות שלה בסביבות הפיתוח והייצור- בפיתוח היא תדפיס הכל למסך למעקב מיידי, ובייצור תכתוב לטבלה רק מה שחשוב; וכך לא יהיה צורך "להעיר" פקודות בקרה כמו פקודות ה-Print הנ"ל עם סיום הפיתוח. למשל-
If Object_ID('P_Maakav') Is Not Null Drop Proc P_Maakav;
Go
Create Proc P_Maakav @ProcID Int,
@Mispar VarChar(10)=Null,
@Pratim Varchar(Max)=Null,
@Pratim_XML XML=Null
As
Declare @S Varchar(Max),
@Object_Name Sysname;
Set @Object_Name=Object_Name(@ProcID);
Insert Into T_Maakav(DB_Name,
Object_Name,
Connection_ID,
Transaction_ID,
Mispar,
Pratim,
Pratim_XML)
Select DB_Name(),
@Object_Name,
Connection_ID,
Transaction_ID,
@Mispar,
@Pratim,
@Pratim_XML
From sys.dm_exec_requests
Where session_id=@@SPID;
If @@ServerName In ('TIOM-3504','MyTestServer') --Select @@ServerName
Begin
Set @S=@Object_Name+IsNull(', '+@Mispar,'')+': @DB_Name='+DB_Name()+', @Object_Name='+IsNull(@Object_Name,'Null')+', @Pratim='+IsNull(@Pratim,'Null')+', @Pratim_XML='+IsNull(Cast(@Pratim_XML As Varchar(Max)),'Null');
Print IsNull(@S,'Null');
End
Go
פרוצדורת המעקב מזהה את שם הפרוצדורה שהפעילה אותה לפי משתנה המערכת @@ProcID שהועבר אליה,
את Connection_ID & Transaction_ID לפי השורה המתאימה ב-dm_Exec_Requests,
ומדפיסה הודעה סטנדרטית למסך רק בשרתי הפיתוח והטסט.
פרוצדורה לדוגמה שמבצעת רק פעולות דיווח פשוטות, כאשר את שורות הדיווח למעקב אני מציין ב-/**/ בתחילתן:
If Object_ID('MyProc0') Is Not Null Drop Proc MyProc0;
Go
Create Proc MyProc0 @Taarih DateTime As
Set NoCount On;
/**/Exec P_Maakav @ProcID=@@ProcID,@Mispar='000010',@Pratim=@Taarih;
Select *
Into #T
From sys.objects
Where modify_date>=@Taarih;
/**/Exec P_Maakav @ProcID=@@ProcID,@Mispar='000020';
Select *
From #T
Order By name;
/**/Exec P_Maakav @ProcID=@@ProcID,@Mispar='000030';
Set NoCount Off;
Go
נפעיל אותה:
Exec MyProc0 '20100101';
Go
המידע במקרה זה נשמר גם בטבלת המעקב (שליפת הריצה של פרוצדורה MyProc0):
Select *
From T_Maakav
Where Object_Name='MyProc0'
Order By Mispar;
Go
הלאה- נמשיך לשימוש יותר מורכב: פרוצדורה שמקבלת כפרמטרים שמות של טבלה ושל עמודה לפיה יש למיינה:
If Object_ID('MyProc1') Is Not Null Drop Proc MyProc1;
Go
Create Proc MyProc1(@Tbl Sysname,
@OrderBy Sysname)
As
Declare @SQL VarChar(Max),
@TraceS Varchar(Max),
@TraceXML XML;
/**/Set @TraceS='Exec MyProc1 @Tbl='+@Tbl+', @OrderBy='+@OrderBy+';'; Exec P_Maakav @@ProcID,'000010',@TraceS,Null;
Set @SQL='Select'+CHAR(9)+'*'+
CHAR(13)+'From'+CHAR(9)+@Tbl+
CHAR(13)+'Order By '+@OrderBy+';'
/**/Set @TraceXML=(Select @SQL SQL For XML Raw('Pratim_XML'), Root('Root')); Exec P_Maakav @@ProcID,'000020',Null,@TraceXML;
Exec(@SQL);
/**/Set @TraceXML=(Select @@RowCount [RowCount] For XML Raw('Pratim_XML'), Root('Root')); Exec P_Maakav @@ProcID,'000030',Null,@TraceXML;
נריץ כדי להדגים מה מבצעת פרוצדורת המעקב P_Maakav המופעלת מתוכה:
Exec MyProc1 'Sys.objects','name';
Go
גם כאן יש שלוש "נקודות דיווח" כמו בפרוצדורה הקודמת,
כאשר בראשונה מוצגת הפקודה שהפעילה את הפרוצדורה (כדי לדעת באילו פרמטרים נעשה שימוש),
בשנייה מוצגת פקודת ה-SQL הדינאמית (תצוגה "מעוצבת" תוצג מתוך טבלת המעקב),
ובשלישית מספר השורות בשליפה.
הפלט במקרה זה לא כל כך קריא, וכדי לשפר אותו אין צורך לשנות את פרוצדורה MyProc1 אלא את פרוצדורת המעקב P_Maakav ומאותו רגע כל הפלטים של כל הפרוצדורות יסתדרו. כך גם אם רוצים להוסיף או לגרוע מידע, ואפילו אם רוצים לא לבצע דבר ולנטרל את הפרוצדורה (נניח בסביבת הייצור אם לא רוצים להכביד על המערכת ואזי היא תכלול רק פקודת Return).
נשלוף כעת את המידע לגבי הריצה של MyProc1 מתוך טבלת המעקב, כולל ה-SQL וה-RowCount מתוך ה-XML:
Select T2.Loc.query('.').value('(/Pratim_XML/@RowCount)[1]','int') [RowCount],
Cast(T2.Loc.query('.').value('(/Pratim_XML/@SQL)[1]','Varchar(Max)') As XML) SQL,
M.*
From T_Maakav M
Outer Apply [Pratim_XML].nodes('/Root/Pratim_XML') As T2(Loc)
Where Object_Name='MyProc1'
Order By Mone,
Mispar;
Go
על ידי הקלקה על פקודת ה-SQL בעמודה השניה – תיפתח לשונית חדשה עם הפקודה המעוצבת.
מה יקרה במקרה של שגיאה? נריץ את הפרוצדורה בזדון עם שם עמודה שאינה קיימת:
Exec MyProc1 'Sys.objects','xxxx';
Go
חוץ מכך שניתן לדעת כעת היכן בדיוק הייתה נפילה, ניתן לגשת לטבלת המעקב ובעזרת השליפה הנ"ל למצוא את פקודת ה-SQL שגרמה לשגיאה.
מה קורה כשיש שגרת שגיאה וכשנעשה שימוש בטרנזקציות?
קודם כל יש ליצור חד פעמית פרוצדורת השלמה לפרוצדורת המעקב, שתופעל מתוך בלוק ה-Catch,
תשמור את המידע שנשלח לטבלת המעקב במהלך הטרנזקציה בתוך משתנה טבלה (כדי שלא יאבד בביצוע Rollback),
תבצע Rollback לטרנזקציה הפתוחה,
תחזיר את מה שבמשתנה הטבלה לטבלת המעקב (מה שהיה שם נעלם בביצוע ה-Rollback),
ותפתח טרנזקציה חדשה שתיסגר בצורה מסודרת מתוך הפרוצדורה המקורית:
If Object_ID('P_Catch') Is Not Null Drop Proc P_Catch;
Go
Create Proc dbo.P_Catch
AS
Declare @T Table(Mone Int,
TaarihMale Datetime,
[DB_Name] Sysname,
[Object_Name] Sysname,
[User_Name] Sysname,
[System_User] Sysname,
[App_Name] Sysname,
[Host_Name] Sysname,
SPID Int,
Connection_ID UniqueIdentifier,
Transaction_ID Bigint,
Mispar VarChar(10),
Pratim Varchar(Max),
Pratim_XML XML);
Insert Into @T(Mone,TaarihMale,[DB_Name],[Object_Name],[User_Name],[System_User],[App_Name],[Host_Name],SPID,Connection_ID,Transaction_ID,Mispar,Pratim,Pratim_XML)
Select Mone,TaarihMale,[DB_Name],[Object_Name],[User_Name],[System_User],[App_Name],[Host_Name],SPID,Connection_ID,Transaction_ID,Mispar,Pratim,Pratim_XML
From T_Maakav
Where Transaction_ID=(Select Transaction_ID From sys.dm_exec_connections Where session_id=@@SPID);
Rollback Tran;
Begin Tran;
Set Identity_Insert T_Maakav On;
Insert Into T_Maakav(Mone,TaarihMale,[DB_Name],[Object_Name],[User_Name],[System_User],[App_Name],[Host_Name],SPID,Connection_ID,Transaction_ID,Mispar,Pratim,Pratim_XML)
Select T2.*
From (Select Mone,TaarihMale,[DB_Name],[Object_Name],[User_Name],[System_User],[App_Name],[Host_Name],SPID,Connection_ID,Transaction_ID,Mispar,Pratim
From @T
Except
Select Mone,TaarihMale,[DB_Name],[Object_Name],[User_Name],[System_User],[App_Name],[Host_Name],SPID,Connection_ID,Transaction_ID,Mispar,Pratim
From T_Maakav) T1
Inner Join @T T2
On T1.Mone=T2.Mone;
Commit;
Begin Tran;
Go
וכעת לפרוצדורה עצמה:
If Object_ID('MyProc2') Is Not Null Drop Proc MyProc2;
Go
Create Proc dbo.MyProc2(@Tbl Sysname,
@OrderBy Sysname)
As
Declare @SQL VarChar(Max),
@TraceS Varchar(Max),
@TraceXML XML;
/**/Set @TraceS='Exec MyProc1 @Tbl='+@Tbl+', @OrderBy='+@OrderBy+';'; Exec P_Maakav @@ProcID,'000010',@TraceS,Null;
Begin Try
Begin Tran
Set @SQL='Select'+CHAR(9)+'*'+
CHAR(13)+'Into'+CHAR(9)+'Tmp'+
CHAR(13)+'From'+CHAR(9)+@Tbl+
CHAR(13)+'Order By '+@OrderBy+';';
/**/Set @TraceXML=(Select @SQL SQL For XML Raw('Pratim_XML'), Root('Root')); Exec P_Maakav @@ProcID,'000020',Null,@TraceXML;
If Object_ID('Tmp') Is Not Null Drop Table Tmp;
Exec(@SQL);
/**/Set @TraceXML=(Select @@ROWCOUNT [RowCount] For XML Raw('Pratim_XML'), Root('Root')); Exec P_Maakav @@ProcID,'000030',Null,@TraceXML;
Select * From Tmp;
Commit Tran;
End Try
Begin Catch
/**/Set @TraceS=IsNull(Object_Name(@@ProcID),'פרוצדורה לא ידועה')+': Error_Procedure()='+IsNull(Error_Procedure(),'פרוצדורה לא ידועה')+', Error_Line()='+Cast(Error_Line() As Varchar(Max))+', Error_Message()='+Cast(Error_Message() As Varchar(Max))+', Error_Severity()='+Cast(Error_Severity() As Varchar(Max));Exec P_Maakav @@ProcID,'900010',@TraceS,Null;
Exec P_Catch;
Rollback;
End Catch
Go
הפרוצדורה דומה לקודמת, אך מכניסה את הנתונים שנשלפו לטבלה, ולאחר מכן שולפת אותם מתוכה (כל זאת כדי שיהיה תוכן לטרנזקציה.
במקרה של שגיאה- מופעלת הפרוצדורה P_Catch.
הרצה תקינה דומה באופן כללי לזו של הפרוצדורה הקודמת ולכן אני נדלג עליה, ונפעיל אותה במכוון באופן שגוי:
Exec MyProc2 'Sys.objects','xxxx';
Go
מהפלט ניתן לראות שלא חזרו נתונים, ושהריצה הגיעה לבלוק ה-Catch.
נציץ בטבלת המעקב:
Select T2.Loc.query('.').value('(/Pratim_XML/@RowCount)[1]','int') [RowCount],
Cast(T2.Loc.query('.').value('(/Pratim_XML/@SQL)[1]','Varchar(Max)') As XML) SQL,
M.*
From T_Maakav M
Outer Apply [Pratim_XML].nodes('/Root/Pratim_XML') As T2(Loc)
Where Object_Name='MyProc2'
Order By Mone,
Mispar;
Go
מופיעים כאן הפרטים של שתי הריצות – התקינה והשגויה,
אפשר לראות את קוד ה-SQL התקין בשורה השניה,
ואת קוד ה-SQL השגוי בשורה החמישית.
שוב- "הפואנטה" בדוגמה זו היא העובדה שהתבצע Rollback לטרנזקציה, אך הנתונים שנשלחו לטבלת המעקב נותרו בה.
לסיום- מה קורה כאשר רוצים לשמור בטבלת המעקב תוכן של טבלה לצורך תחקור?
If Object_ID('MyProc3') Is Not Null Drop Proc MyProc3;
Go
Create Proc dbo.MyProc3
As
Declare @TraceXML As XML;
Select object_id,
name
Into #T
From sys.objects
Where Object_ID<100;
/**/Set @TraceXML=Cast((Select * From #T For XML Raw('Pratim_XML'), Root('Root')) As XML); Exec P_Maakav @@ProcID,'000010',Null,@TraceXML;
Go
הפרוצדורה מבצעת שליפה לטבלה זמנית,
ומפעילה את פרוצדורת המעקב כך שתוכן הטבלה הזמנית ישמר בתוך שדה ה-XML.
נפעיל את הפרוצדורה:
Exec MyProc3;
Go
נשלוף את נתוני הטבלה שנשמרו מתוך ה-XML:
Select T2.Loc.query('.').value('(/Pratim_XML/@object_id)[1]','Int') [object_id],
T2.Loc.query('.').value('(/Pratim_XML/@name)[1]','Varchar(Max)') [name],
T_Maakav.*
From T_Maakav
Outer Apply [Pratim_XML].nodes('/Root/Pratim_XML') as T2(Loc)
Where Object_Name='MyProc3'
Order By name,
Mone;
כדאי לשים לב לכך שלכל השורות אותו ערך בעמודת Mone וזה מפני שמדובר בשורה אחת מהטבלה,
ושתי העמודות הראשונות הן מתוך ה-XML של שורה זו.
