1
00:00:00,000 --> 00:00:06,230
(Music)

2
00:00:06,230 --> 00:00:08,940
Hello and welcome.

3
00:00:08,940 --> 00:00:10,425
In this video,

4
00:00:10,425 --> 00:00:12,630
we'll look at how to get information about

5
00:00:12,630 --> 00:00:15,975
tables and their columns in a database.

6
00:00:15,975 --> 00:00:20,785
Now how would you get a list of tables in the database?

7
00:00:20,785 --> 00:00:24,120
Sometimes your database may contain several tables,

8
00:00:24,120 --> 00:00:26,445
and you may not remember the correct name.

9
00:00:26,445 --> 00:00:28,560
For example, you may wonder

10
00:00:28,560 --> 00:00:30,690
whether the table is called dog,

11
00:00:30,690 --> 00:00:34,095
dogs or four legged mammals.

12
00:00:34,095 --> 00:00:36,750
Database systems typically contain

13
00:00:36,750 --> 00:00:38,830
system or catalog tables,

14
00:00:38,830 --> 00:00:40,610
from where you can query

15
00:00:40,610 --> 00:00:43,610
the list of tables and get their properties.

16
00:00:43,610 --> 00:00:48,200
In DB2 this catalog is called syscat tables.

17
00:00:48,200 --> 00:00:50,375
In SQL Server, it's

18
00:00:50,375 --> 00:00:54,140
information schema tables, and in Oracle

19
00:00:54,140 --> 00:00:57,560
it's all tables or user tables.

20
00:00:57,560 --> 00:01:01,670
To get a list of tables in a DB2 database,

21
00:01:01,670 --> 00:01:04,100
you can run the following query.

22
00:01:04,100 --> 00:01:07,865
Select star from syscat tables.

23
00:01:07,865 --> 00:01:10,250
This select statement will return

24
00:01:10,250 --> 00:01:13,070
too many tables including system tables,

25
00:01:13,070 --> 00:01:16,640
so it's better to filter the result as shown here.

26
00:01:16,640 --> 00:01:19,685
Select tabschema, tabname,

27
00:01:19,685 --> 00:01:23,285
create underscore time from syscat tables,

28
00:01:23,285 --> 00:01:28,315
where tabschema equals ABC12345.

29
00:01:28,315 --> 00:01:30,780
Please ensure that you replace

30
00:01:30,780 --> 00:01:36,510
ABC12345 with your own DB2 username.

31
00:01:36,510 --> 00:01:40,315
When you do a select star from syscat tables,

32
00:01:40,315 --> 00:01:43,160
you get all the properties of the tables.

33
00:01:43,160 --> 00:01:45,260
Sometimes we're interested in

34
00:01:45,260 --> 00:01:48,335
specific properties such as creation time.

35
00:01:48,335 --> 00:01:50,015
Let's say you've created

36
00:01:50,015 --> 00:01:52,355
several tables with similar names.

37
00:01:52,355 --> 00:01:54,290
For example, dog one,

38
00:01:54,290 --> 00:01:59,545
dog underscore test, dog test one and so on. But,

39
00:01:59,545 --> 00:02:01,910
you want to check which of these tables was

40
00:02:01,910 --> 00:02:05,330
the last one you created; to do so,

41
00:02:05,330 --> 00:02:09,395
you can issue a query like select tabschema,

42
00:02:09,395 --> 00:02:14,720
tabname, create underscore time from syscat tables:

43
00:02:14,720 --> 00:02:20,530
Where a tabschema equals QCM54853.

44
00:02:20,530 --> 00:02:23,495
The output will contain the schema name,

45
00:02:23,495 --> 00:02:25,910
table name, and creation time

46
00:02:25,910 --> 00:02:28,295
for all tables in your schema.

47
00:02:28,295 --> 00:02:30,620
Next, let's talk about how to

48
00:02:30,620 --> 00:02:32,815
get a list of columns in a table.

49
00:02:32,815 --> 00:02:34,610
If you can't recall

50
00:02:34,610 --> 00:02:37,460
the exact name of a column for example,

51
00:02:37,460 --> 00:02:40,070
whether it had any lowercase characters

52
00:02:40,070 --> 00:02:42,285
or an underscore in its name,

53
00:02:42,285 --> 00:02:46,435
in DB2 you can issue a query like the one shown here.

54
00:02:46,435 --> 00:02:49,325
Select star from syscat columns

55
00:02:49,325 --> 00:02:52,055
where tab name equals dogs.

56
00:02:52,055 --> 00:02:55,040
For your information, in my SQL,

57
00:02:55,040 --> 00:02:59,285
you can simply run the command show columns from dogs,

58
00:02:59,285 --> 00:03:01,130
or you may want to know

59
00:03:01,130 --> 00:03:03,710
specific properties like the datatype

60
00:03:03,710 --> 00:03:05,735
and length of the datatype.

61
00:03:05,735 --> 00:03:08,000
In DB2, you can issue

62
00:03:08,000 --> 00:03:11,239
a statement like, select distinct name,

63
00:03:11,239 --> 00:03:14,585
coltype, length from sysibm,

64
00:03:14,585 --> 00:03:18,860
syscolumns where tbname equals dogs.

65
00:03:18,860 --> 00:03:20,945
Here we look at the results

66
00:03:20,945 --> 00:03:23,030
of retrieving column properties,

67
00:03:23,030 --> 00:03:24,470
for a real table called

68
00:03:24,470 --> 00:03:28,165
Chicago Crime Data from a Jupyter notebook.

69
00:03:28,165 --> 00:03:29,960
Notice in the output,

70
00:03:29,960 --> 00:03:33,815
you can tell certain column names show different cases.

71
00:03:33,815 --> 00:03:39,410
For example, the column titled arrest has an uppercase A,

72
00:03:39,410 --> 00:03:42,290
and the rest of the characters are lowercase.

73
00:03:42,290 --> 00:03:44,720
So, keep in mind that when you

74
00:03:44,720 --> 00:03:47,224
refer to this column in your query,

75
00:03:47,224 --> 00:03:49,100
not only must you enclose

76
00:03:49,100 --> 00:03:51,920
the word arrest within double quotes,

77
00:03:51,920 --> 00:03:53,959
you must also preserve

78
00:03:53,959 --> 00:03:56,830
the correct case inside the quotes.

79
00:03:56,830 --> 00:03:59,270
In this video, we saw how to

80
00:03:59,270 --> 00:04:02,255
retrieve table and column information.

81
00:04:02,255 --> 00:04:04,530
Thanks for watching.

82
00:04:04,530 --> 00:04:09,000
(Music)