Mainframe DB2

hello and welcome to the wild and wacky world of IBM mainframe operating systems today we're looking at OSD 90 running on an emulated s/390 mainframe but everything you're about to see is essentially exactly the same on the latest and greatest version of the z/os operating system and on the multi-million dollar system z mainframes that you can buy from IBM today there's a direct path back to the MVS operating system that was developed for and available on the original s 360 mainframe in the 60s and the fanatical backward compatibility of just sort of building from that

and making everything work the same way will rear its head in interesting ways here so first we'll log on in our 3270 terminal emulator vlog on to TSO the time sharing option which presents an interactive way to work with the mainframe instead of strictly through batch jobs that were submitted through card readers and Monroe logged-in will start up ispf the I think is the interactive system productivity facility so this is really a developer and system manager oriented tool to develop applications on the mainframe so this is PF product is what has the text editor for

example the file editor as well as utilities and some processes to help you run your various compilers but today we're just interested in running some sequel commands against db2 db2 for the mainframe is also running on this emulated system under OS 390 so to run sequel I need to put my sequel statements in a file and then I will execute that file against the database server you don't just create a file on a mainframe and arbitrarily fill it up with data as needed you have to allocate a data set so they're called data sets on

the mainframe not files and you have to pre allocate space for the data set and you have to define some of the physical characteristics of that data set up front before you can then start putting any data in it so ispf provides a data set utility so utilities was number three and then data set utility is number two option a allocates a data set and I'm gonna call this data set in Wilson dot let's call it sequel commands if I hit enter there we're gonna allocate a data set named in Wilson not sequel commands there

isn't a hierarchical file system or anything on the mainframe under under OS 390 and other MVS derived operating systems there are just data sets with names on hard drives and if you look at a particular hard drive you can see all the data sets that are in it but if I don't know what hard drive a data set is on there are also catalogs and the catalogs are essentially indexes of all of the data sets with pointers to the right drive so that I can refer to this data set by the name and we'll send

out sequel commands without also having to remember that oh I created it on the volume user 0 1 so the hard drives on mainframes are called as DS DSD direct access storage devices and in this case I'm gonna put it on a dazz D volume I have called use 0 1 but then I also have to allocate the space for it and I can refer to space in terms of blocks tracts or cylinders newer versions of the operating system like OS 390 let me work in terms of kilobytes and megabytes however these don't necessarily line

up too nice you know round volumes of cylinders and tracks and the underlying hard drive and the hard drive controllers and the way the storage allocation really works is in terms of tracks or cylinders there's a certain number of blocks in a track a certain number of tracks in a cylinder and those aren't gonna line up with any nice round numbers if I start working in megabytes and kilobytes so will stick was working with tracks well how many bytes are in a track you might ask it's a good question and there isn't one answer because

that's entirely dependent on the geometry of the particular model of hard drive of the volume that you're creating this dataset on so you zero one happens to be a 3390 volume that's the current kind of modern standard that again dates back to OS 390 but I think even even current systems II drive controllers emulate the 3390 geometry so there there will be some particular number of bytes per track some particular number of tracks per cylinder and then a total number of cylinders available on the volume but we're just gonna say track we're gonna say that

I want to allocate 10 tracks on this Daz d this hard drive for my dataset if I fill those 10 tracks up I can add secondary extents and I'll do that in units of 5 tracks at a time and I want to reserve 10 blocks in this data set for a directory now this lets me use this dataset more as a folder or a directory than a single flat file and so this helps with with organization a little bit instead of a mass proliferation of just this flat structure of files on a hard drive that

that follow this naming convention of kind of up to eight characters dot another eight characters dot another eight characters to kind of imply a hierarchy and the end user tools treat that as a hierarchy but fundamentally it's just one list of files on a volume so inside of it I can say you know what I want to create named members inside of this file that I can address kind of as individual files so we'll resort in directory blocks for the the index of those things within this file this is gonna be a fixed block record

format file that means that every record in the file is 80 bytes long you know we're not delimiting lines by carriage return or line feed this is essentially just card punched card images which were 80 characters wide and most of the files you work with on a mainframe that look like text files so source files like our sequel commands or COBOL or Fortran or C or you know whatever whatever the file may be JCL is 80 columns wide you tend to work with these fixed block a byte records block size is when I'm fetching records

from the physical disk how much data do I read at once in one fetch and you know if I want it to read a hundred records at a time off of the the real physical storage I can make that 8,000 so just you know 80 by 100 I want to leave that blank and the system will pick the value for me depending on the hard drive type and the size of my records there is some more optimal and less optimal block sizes just for the efficiency of how the system deals with the data if I

leave this blank it should get close to the optimum size if I hit enter there now allocated my data set we can see that message up here now that that's allocated I can list my data set so I can say show me every every data set that starts with M Wilson and I can see everything that starts with M Wilson and there's my new sequel commands data set I scroll to the right with F 11 we can see that it is in fact 10 tracks which is what I requested 10% used so this data set

is using 10% of its allocated space of 10 tracks that would be that directory area that I pre-allocated so that I can use this as a partitioned data set instead of just a single flat sequential data set currently it exists in one extent so we haven't added a second extent which would give me another 5 tracks worth of space and it's on a 3390 device which again is important because well how much space is 10 tracks unless you know the actual device and then if you look at the specs for the device to see the

physical characteristics that's the only way you'll know how much space is 10 tracks and it doesn't really do that math for me if I get more info on this I don't think it's gonna tell me anywhere like oh this is you know half a megabyte I just I have to know the characteristics of a 3390 device and I have to run the math myself you can see it gave us that block size of 20 7920 based on the fact that say 3390 and i have records of 80 anyway this is all a bit of a

tangent but you have to pre allocate your input sequel data set for the tool that we're gonna use so now that that's allocated I have essentially an empty file that can hold sub files I'm gonna called members that we can put our sequel in so let's get to the good stuff let's go back here to more and we'll use the db2 products and spoofy is the tool to actually process sequel statements so my source dataset so we created it it was called sequel C M DS and then in parentheses we have the member name so

let's say create table one two three four okay I can afford one more character here for the eight character limit on every name segment so create table and because it's cataloged I don't need a volume serial number catalogs and how you find files is a whole other thing data set password protection really isn't used anymore there's a whole other security product called rack F for the OS 390 and z/os operating systems so data set passwords aren't used but this is a relic of when you know an individual user to prevent other users from being able

to read what's in a data set could just set a password on a data set and if you didn't know the password you couldn't open up that data set so this is one of those relics of times gone by output this will be a sequential data set unlike the input the tool will allocate this for me so we'll just leave that as a mail send out sequel that output that worked before don't need to change the defaults we want to edit the input and then we won't execute it and then if it's exceeded will auto

commit it and then we'll look at the output of the sequel commands when we're done hit enter here we end up in the editor editing and we'll send out sequel commands member name create table this is the ispf editor so if you are editing files on the mainframe this is probably what you're using and it's kind of a weird it's VI like and that you can put line commands in this red area but it's not VI like and that it's not modal you're not in like insert mode versus command mode so the red is where

the line commands go under the blue is where the actual text goes so we're working with sequel I can tell it you know what I want this to be mixed case so I think I can say caps off otherwise that will just convert everything I type into little letters because you know until recently you know punch cards didn't have uppercase and lowercase it was all capital so the mainframe operating systems most things tend to be in capital letters this is where it's kind of like said VI like I say I 24 insert 20 lines that

gives me 20 new lines to work with here and I will just start typing my sequel so create table let's say I want to keep track my favorite books will have maybe an ID which is an integer and say not null and then my title varchar' I have a lot of book titles also not null and author and yeah maybe maybe we allow a null author because maybe it's published anonymously or something like that who knows give it a primary primary key for the ID column that should do it for that now unlike most database

systems I've worked with we have to say in database and then my database name so mr w DB double O one no semicolon into that statement we can use comment lines sequel comments or two dashes if I don't say in database mrw DB o 1 everything applies to the default database which is just a system-wide default database that you may or may not have permission to do things in so there is no context where you know I'm connecting to a specific database on the server I actually have to qualify this command when I'm creating a

table with well ok what database do you want to create that table in now as a user I'm running this as my user in Wilson it will namespace qualify this table for me so it will be why can't I go into insert mode you know it will be in Wilson dot books behind the scenes but I can just go ahead and leave it as ebooks for now and it'll do that so once I've done that I should be able to insert data right so insert into books' values let's say book 1 is my neat book

by Matthew Wilson you can tell I'm going to be a great author insert into books' values let's do another one another thriller by I don't know James Smith that looks good so I'm creating my table and I am inserting a couple of initial records into it so if I exit the editor it tells me that the Edit session has completed press ENTER to continue that's to continue on to the execute phase so we will execute this and now we get the output so it echoes back the statement that was input we see statement execution was

successful for our create table books that's good insert into books' uh-oh air - 540 the definition of table in Wilson dot books is incomplete because it lacks a primary index or a required unique index so even though I said primary key ID db2 doesn't automatically create the index on the primary key for me and I don't remember I mentioned earlier either primary key doesn't imply not null if I said ID int primary key here I would have gotten an error that said you can't create primary key on a column that's not null so everything is

very very explicit no indexes are created automatically you know no column becomes not null because I said it's the primary key things you're used to in other databases don't happen here and again that seems to be the theme of the mainframe right it was like oh you have to pre allocate your datasets your files and you have to say how much space you want to allocate for them and how you physically want them arranged yeah and welcome to mainframes so that failed so it performed a rollback that rollback succeeded so I guess that's good sequel

statements assumed to be between columns 1 and 72 well look at why that is in a moment even though this we said was an 80 an 80 byte record sighs okay so we exit out of that and if I just hit enter again now we're back in the Edit phase I'm back in that same file that we saved before and we know we need to create our index so create index will call it books primary key on books ID and actually we need to say create e and this is I have to delete space off

the end of this line here so because the line can't be more than 80 columns wide so now that I deleted that empty space I can flip over into insert mode here and create there we go create unique index books PK on books id sound good I think so we will exit we will hit enter again to execute that file we just edited and let's see what happens table created successfully remember we rolled back the last transactions so we're not getting a duplicate table error here create index successful in certain books successful insert books successful

excellent all that was successful so we committed the transaction and that's the end of the info so now as one last thing let's say we want to we want to keep that create commander and so we'll make another member for select data and this we will just say select star from books and exit out of that run that and there we are so let's start from books we get the nice column header ID title author I can see my books and that's pretty much what we expected that matches what our inputs were another thing we

can look at db2 admin again who needs management studio when you have this you know Great Council based interface and you're just making files for your sequel input running them seeing text files of the sequel output of that series of commands this is great so efficient db2 system catalog I can see for example the databases in my system look at that we can actually we can restrict this to that database mrw DBW one there's the database that we were just working with I can display the tables in that database I can see we have our

books table that's good I can look at the columns that are defined in that table there we go there's our columns who needs a sidebar with with all that information when you can just just browse around this way right if I look at yes I'm booked so so from right here I can I can drop that table drop table and Wilson dot books boom gone yeah so if we go back to our data set list in Wilson we can see now my my output file is still there right that's just it saved my last output

to a file if we look at my partition data set we have the two members create table which was the first one I created and then that last one select table right if I look at this file we have the text that I saw but remember we had those six or seven columns for our character positions that were where my line commands go so we weren't seeing all 80 characters of our records we were only seeing 72 characters of our records when we were in the editor well the last positions 73 through 80 our line

numbers card numbers and the sequel processor knows to only use positions 1 through 72 as sequel input because the rest of the record are these sequential numbers or the the ordered numbers and this was for when you dropped your stack of punched cards and they got out of order you always punched them with a sequence number on the end so you could drop them in the physical card sorter and then get your cards back in the correct position so the editor behind the scenes because even though this is all just files on hard drives it

still thinks we're in the punched card world because we're on a mainframe the editor put in these numbers for me and when we went back later and added a new rows like to create that unique index on on books for the primary key you can see that we ended up you know inserting numbers between the sequence of numbers that were there and there's renumber commands if you want to get a nice new you know set of numbering from the start but it's just it's kind of funny to see that after you're editing the file and

you just come back and view the file not in the editor you you have your card numbers so that if you drop your your stack of cards on the floor you can drop it back in the sorter and get them back in the right order so that's about all there is to show here yeah wild and crazy world of the mainframe we will log off and you can get back to your regularly scheduled modern computing