הבלוג של גרי רשף

01/05/2011

מערכת למעקב אחר ריצת פרוצדורות

Filed under: Uncategorized — תגיות: , , , , — גרי רשף @ 16:42

לא פעם אנחנו נזקקים למעקב ותיעוד ריצות של פרוצדורות או קטעי קוד אחרים (סקריפטים, טריגרים, ג'ובים..), מסיבות שונות כגון:

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

clip_image002

המידע במקרה זה נשמר גם בטבלת המעקב (שליפת הריצה של פרוצדורה MyProc0):

Select    *
From    T_Maakav
Where    Object_Name='MyProc0'
Order By Mispar;
Go

clip_image004

הלאה- נמשיך לשימוש יותר מורכב: פרוצדורה שמקבלת כפרמטרים שמות של טבלה ושל עמודה לפיה יש למיינה:

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

clip_image006

גם כאן יש שלוש "נקודות דיווח" כמו בפרוצדורה הקודמת,

כאשר בראשונה מוצגת הפקודה שהפעילה את הפרוצדורה (כדי לדעת באילו פרמטרים נעשה שימוש),

בשנייה מוצגת פקודת ה-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

clip_image008

על ידי הקלקה על פקודת ה-SQL בעמודה השניה – תיפתח לשונית חדשה עם הפקודה המעוצבת.

clip_image010

מה יקרה במקרה של שגיאה? נריץ את הפרוצדורה בזדון עם שם עמודה שאינה קיימת:

Exec MyProc1 'Sys.objects','xxxx';
Go

clip_image012

חוץ מכך שניתן לדעת כעת היכן בדיוק הייתה נפילה, ניתן לגשת לטבלת המעקב ובעזרת השליפה הנ"ל למצוא את פקודת ה-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

clip_image014

מהפלט ניתן לראות שלא חזרו נתונים, ושהריצה הגיעה לבלוק ה-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

clip_image016

מופיעים כאן הפרטים של שתי הריצות – התקינה והשגויה,

אפשר לראות את קוד ה-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

clip_image018

נשלוף את נתוני הטבלה שנשמרו מתוך ה-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;

clip_image020

כדאי לשים לב לכך שלכל השורות אותו ערך בעמודת Mone וזה מפני שמדובר בשורה אחת מהטבלה,
ושתי העמודות הראשונות הן מתוך ה-XML של שורה זו.

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: