Rearrange hex digits.

kyle has asked for the wisdom of the Perl Monks concerning the following question:

There's a long preamble to my actual question because I think the story of how I got to the question is more interesting than the question itself. If you disagree, you're welcome to skip it.

My ultimate goal (for those wishing to know the XY Problem) is to automate a report of database activity. We're using MS SQL Server at $work, and it has gobs of SQL Server Agent jobs set up to run, and once in a while we'd like to know which ones are running.

When I asked our dynamite DBA how to get the list manually, she directed me to the stored procedure "msdb.dbo.sp_help_job". Getting the results of that via trusty DBD::Sybase proved difficult. It's also not straight forward to use the stored procedure as a subquery that I could just SELECT out of.

The best resource I found for how to do that anyway was How to share data between stored procedures. I wanted to use the INSERT-EXEC method, but it can't be nested, and "msdb.dbo.sp_help_job" is using it already.

I looked into the code for "msdb.dbo.sp_help_job" and found that under everything it calls "master.dbo.xp_sqlagent_enum_jobs", which is an undocumented call to a DLL. It turns out that information about running jobs is not in the system tables (where I'd expect it). Instead, it's cached, and nothing is written to tables you can query until the job is finished. That's why it winds up having to go to some non-SQL to get information about them.

Fortunately I had the code for the stored procedure that calls that non-SQL, so I could see how to get data out of it. I did a cut-and-paste of some code that creates a temporary table to collect results via INSERT-EXEC. That left just one more problem.

The job_id that it returns does not match the ones in msdb.dbo.sysjobs, which is where I need to go to get their names. What it gave me was "5c5d38dd01d7c04ab50504e417dbbebf", but what I needed was "DD385D5C-D701-4AC0-B505-04E417DBBEBF". Luckily I found an article that describes how to do the translation in SQL. After some head-scratching, I was able to come up with some more or less equivalent Perl.

I connect with DBI this way:

    my $dbh = DBI->connect(
        "dbi:Sybase:server=$server",
        $u, $p, { RaiseError => 1 },
    );
    $dbh->do('use msdb');
[download]

The sub that does the work I've been writing about looks like this:

sub get_running_job_info {
    my ($dbh) = @_;

    $dbh->do( <<'END_TEMP_TABLE' );
      CREATE TABLE #tmpy (
         job_id                UNIQUEIDENTIFIER NOT NULL,
         last_run_date         INT              NOT NULL,
         last_run_time         INT              NOT NULL,
         next_run_date         INT              NOT NULL,
         next_run_time         INT              NOT NULL,
         next_run_schedule_id  INT              NOT NULL,
         requested_to_run      INT              NOT NULL, -- BOOL
         request_source        INT              NOT NULL,
         request_source_id     sysname          COLLATE database_defau
+lt NULL,
         running               INT              NOT NULL, -- BOOL
         current_step          INT              NOT NULL,
         current_retry_attempt INT              NOT NULL,
         job_state             INT              NOT NULL
      )
END_TEMP_TABLE
    ;

# It's entirely possible this will break if run on some other version 
+of MS SQL
    $dbh->do(q{INSERT #tmpy EXECUTE master.dbo.xp_sqlagent_enum_jobs 1
+, 'sa'});

    my $sql = 'SELECT job_id,current_step FROM #tmpy WHERE running != 
+0';
    my @job_info = @{$dbh->selectall_arrayref( $sql, {Slice=>{}} )};
    for my $info_ref ( @job_info ) {
        my $job_id = $info_ref->{job_id};

        $job_id =~ tr/a-z/A-Z/;
        $job_id =~ s/^(..)(..)(..)(..)(..)(..)(..)(..)(.{4})(.{12})$/$
+4$3$2$1-$6$5-$8$7-$9-$10/;

        my $name_sql
            = "SELECT [name] FROM msdb.dbo.sysjobs WHERE job_id = '$jo
+b_id'";
        my ($job_name) = $dbh->selectrow_array( $name_sql );
        $info_ref->{name} = $job_name // $info_ref->{job_id};
    }

    return \@job_info;
}
[download]

After all that my real question is how to improve this little part:

        $job_id =~ tr/a-z/A-Z/;
        $job_id =~ s/^(..)(..)(..)(..)(..)(..)(..)(..)(.{4})(.{12})$/$
+4$3$2$1-$6$5-$8$7-$9-$10/;
[download]

That's the part that turns "5c5d38dd01d7c04ab50504e417dbbebf" into "DD385D5C-D701-4AC0-B505-04E417DBBEBF". I suspect that what I'm doing can be done better with pack/unpack, but I don't know a lot about those. I'd settle for a more comprehensible regular expression. I'm using Perl 5.10, so use those features if you want them.

I figure also that this translation has a good name that I don't know. I expect I might change the title of my post if someone can tell me what it is.

Thank you for your attention.

Comment on Rearrange hex digits. Select or Download Code

Replies are listed 'Best First'.
Re: Rearrange hex digits. by ikegami (Patriarch) on May 24, 2010 at 16:08 UTC
`my $uuid = sprintf('%08X-%04X-%04X-%04X-%04X%08X', unpack 'V v v n nN', pack 'H*', $_);` [download] Not that unreadable (relatively speaking). With 'n' and 'v', the size of the letter (lc vs uc) indicates the size of the field (16 vs 32 bit). 'n' keep order, 'v' reverses it.	[reply] [d/l]
Re: Rearrange hex digits. by kennethk (Abbot) on May 24, 2010 at 14:49 UTC
I don't see any way of modifying the algorithm of the regular expression to make it more comprehensible (maybe doing it in multiple steps?) but, when I want to make code more comprehensible, my first thought is documentation, and regular expressions can certainly take documentation. Utilizing the `x` modifier, the regular expression will ignore white space and comments (# delimited). See perlre. I also think uc might be more clear than your tr. Consider: `$job_id = uc $job_id; $job_id =~ s/^(..)(..)(..)(..) # Invert first 4 bytes (..)(..) # More comments ... (..)(..) (.{4}) (.{12}) $ /$4$3$2$1-$6$5-$8$7-$9-$10/x;` [download] or the indentation/comment scheme of your choice.	[reply] [d/l] [select]