miércoles, 12 de marzo de 2014

user-list.pl - programa en Perl para leer un formato determinado y crear una lista de usuarios

use strict;
use feature "switch";
open my $outfh, '>:encoding(utf8)', 'user-list.txt';
my %NOT_COLLECTED;
my @UNCLASSIFIED_FILE;
sub print_not_collected {
  print $outfh "-----------------------------------------\n";
  for (keys %NOT_COLLECTED) {
    print $outfh $_ . ' ' . join(' ', keys %{+{map +($_ => undef), @{$NOT_COLLECTED{$_}}}}) . "\n";
  }
  print $outfh "-----------------------------------------\n";
  print $outfh join "\n", @UNCLASSIFIED_FILE;
  close($outfh);
  exit(0);
}

use sigtrap 'handler' => \&print_not_collected, qw(INT QUIT);

my @tmp_PIV;
while(<user-pivot-*.txt>) {
  my ($class) = /\d+/g;
  open my $ph, "<:encoding(utf8)", $_;
  push @tmp_PIV, \%{+{map {/(.*)/; $1=>$class} grep /.+/, <$ph>}};
}
my %DUP;
for my $i (0..$#tmp_PIV-1) {
  for my $j ($i+1..$#tmp_PIV) {
    for (keys %{$tmp_PIV[$i]}) {
      $DUP{$_}{$tmp_PIV[$i]{$_}} = $tmp_PIV[$j]{$_} if exists $tmp_PIV[$j]{$_};
    }
  }
}
for my $name (keys %DUP) {
    for my $i (keys %{$DUP{$name}}) {
        print "$name $i ${DUP{$name}{$i}}\n";
    }
}
die "Duplicated class entries." unless (keys %DUP)==0;
my %PIV_EQ_CLASS;
for (@tmp_PIV) {
  %PIV_EQ_CLASS = (%PIV_EQ_CLASS, %{$_});
}
my %NOT_PIV;
open my $nph, "<:encoding(utf8)", "user-not-pivot.txt";
%NOT_PIV = %{+{map {/(.*)/; $1=>0} grep /.+/, <$nph>}};
$NOT_PIV{''} = 0;   # we don't want to consider blank pivots
my @tmp = grep exists($NOT_PIV{$_}), keys %PIV_EQ_CLASS;
die 'Ambiguity pivot not pivot: ' . join "\n", @tmp unless @tmp==0;
local $/ = undef;
while(glob "HTML/**") {
  print "$_\n";
  open my $fh, "<:encoding(utf8)", $_;
  my $fname = $_;
  my $text = <$fh>;
  $text =~ /<!-- Parsed text .*-->\n/g;
  my @tmp = $text =~ /<!-- .* -->/g;
  my $text2 = join '', map {; s/<!-- //; s/ -->//; $_; } @tmp;
  $text2 =~ s/- ( *)(?=-)/-$1/g;
  my @FILE_CLASS;
  for ($text2 =~ /FriendlyName="(.*?)"/g) {
    push @FILE_CLASS, $PIV_EQ_CLASS{$_} if exists $PIV_EQ_CLASS{$_};
    push @{$NOT_COLLECTED{$_}}, $fname if not exists $PIV_EQ_CLASS{$_} and not exists $NOT_PIV{$_};
  }
  print $outfh $fname . ' ' . join(' ', keys %{+{map +($_ => undef), @FILE_CLASS}}) . "\n" unless @FILE_CLASS==0;
  push @UNCLASSIFIED_FILE, $fname if @FILE_CLASS==0;
}

print_not_collected;

lunes, 10 de marzo de 2014

DB2 - Consultas de metadatos - Consultas recursivas



DB2 – consulta de metadatos

Catalog Schema: Doc original / standard name: syscat à DB2 alias: ibm

triggers
select text from sysibm.sysTRIGGERS where tbname

secuencias
select * from sysibm.syssequences where owner

stored procedure / function
select * from SYSIBM.sysroutines where owner

PK, unique constraint, FK,
select * from SYSIBM.syskeycoluse where constname
select * from SYSIBM.SYSRELS where RELNAME
select * from SYSIBM.SYSFOREIGNKEYS where relname

tables
FROM SYSIBM.SYSTABLES 
FROM SYSIBM.SYSCOLUMNS where tbname and tbcreator

Dummy
sysibm.sysdummy1

Consultas recursivas en DB2 (jerarquía jerárquicas hierarchy hierarchial recursive)

/************************************************************************/
/************************************************************************/
/******************* COMPARE THE FOLLOWING 2 QUERIES  *******************/
/************************************************************************/
/************************************************************************/
-- TOP-DOWN
with MY_HIERARCHY (MY_NODE, MY_PARENTNODE) as (
      -- non-recursive exit
      select ROOT.MY_NODE, ROOT.MY_PARENTNODE
      from THETABLE ROOT where MY_NODE='value'   -- the root node in the tree

      union all
      -- recursive loop (it needs the non-recursive part in order not to be an empty set)
      select CHILD.MY_NODE, CHILD.MY_PARENTNODE
      from MY_HIERARCHY PARENT, THETABLE CHILD
      where CHILD.MY_PARENTNODE = PARENT.MY_NODE   -- top-down join

)
select * from MY_HIERARCHY


-- BOTTOM-UP
with MY_HIERARCHY (MY_NODE, MY_PARENTNODE) as (
      -- non-recursive exit
      select LEAF.MY_NODE, LEAF.MY_PARENTNODE
      from THETABLE LEAF where MY_NODE='value'   -- the leaf node in the tree

      union all
      -- recursive loop (it needs the non-recursive part in order not to be an empty set)
      select CHILD.MY_NODE, CHILD.MY_PARENTNODE
      from MY_HIERARCHY PARENT, THETABLE CHILD
      where CHILD.MY_NODE = PARENT.MY_PARENTNODE   -- bottom-up join

)
select * from MY_HIERARCHY